1<?php
2/**
3 *
4 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
5 * @author     Andreas Gohr <andi@splitbrain.org>
6 */
7// must be run within Dokuwiki
8if(!defined('DOKU_INC')) die();
9
10class syntax_plugin_semanticdata_table extends DokuWiki_Syntax_Plugin {
11
12	/**
13	 * will hold the data helper plugin
14	 */
15	var $dthlp = null;
16
17	/**
18	 * Constructor. Load helper plugin
19	 */
20	function syntax_plugin_semanticdata_table(){
21		$this->dthlp =& plugin_load('helper', 'semanticdata');
22	}
23
24	/**
25	 * What kind of syntax are we?
26	 */
27	function getType(){
28		return 'substition';
29	}
30
31	/**
32	 * What about paragraphs?
33	 */
34	function getPType(){
35		return 'block';
36	}
37
38	/**
39	 * Where to sort in?
40	 */
41	function getSort(){
42		return 155;
43	}
44
45	/**
46	 * Connect pattern to lexer
47	 */
48	function connectTo($mode) {
49		$this->Lexer->addSpecialPattern('----+ *datatable(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_semanticdata_table');
50	}
51
52
53	/**
54	 * Handle the match - parse the data
55	 *
56	 * This parsing is shared between the multiple different output/control
57	 * syntaxes
58	 */
59	function handle($match, $state, $pos, &$handler){
60		// get lines and additional class
61		$lines = explode("\n",$match);
62		array_pop($lines);
63		$class = array_shift($lines);
64		$class = preg_replace('/^----+ *data[a-z]+/','',$class);
65		$class = trim($class,'- ');
66
67		$data = array('classes' => $class,
68                      'limit'   => 0,
69                      'headers' => array());
70
71		// parse info
72		foreach ( $lines as $line ) {
73			// ignore comments
74			$line = preg_replace('/(?<![&\\\\])#.*$/','',$line);
75			$line = str_replace('\\#','#',$line);
76			$line = trim($line);
77			if(empty($line)) continue;
78			$line = preg_split('/\s*:\s*/',$line,2);
79			$line[0] = strtolower($line[0]);
80
81			$logic = 'OR';
82			// handle line commands (we allow various aliases here)
83			switch($line[0]){
84				case 'select':
85				case 'cols':
86				case 'field':
87				case 'col':
88					$cols = explode(',',$line[1]);
89					foreach($cols as $col){
90						$col = trim($col);
91						if(!$col) continue;
92						$column = $this->dthlp->_column($col);
93						$data['cols'][$column['key']] = $column;
94					}
95					break;
96				case 'title':
97					$data['title'] = $line[1];
98					break;
99				case 'head':
100				case 'header':
101				case 'headers':
102					$cols = explode(',',$line[1]);
103					foreach($cols as $col){
104						$col = trim($col);
105						$data['headers'][] = $col;
106					}
107					break;
108				case 'min':
109					$data['min']   = abs((int) $line[1]);
110					break;
111				case 'limit':
112				case 'max':
113					$data['limit'] = abs((int) $line[1]);
114					break;
115				case 'order':
116				case 'sort':
117					$column = $this->dthlp->_column($line[1]);
118					$sort = $column['key'];
119					if(substr($sort,0,1) == '^'){
120						$data['sort'] = array(substr($sort,1),'DESC');
121					}else{
122						$data['sort'] = array($sort,'ASC');
123					}
124					break;
125				case 'where':
126				case 'filter':
127				case 'filterand':
128				case 'and':
129					$logic = 'AND';
130				case 'filteror':
131				case 'or':
132					if(!$logic) $logic = 'OR';
133					$flt = $this->dthlp->_parse_filter($line[1]);
134					if(is_array($flt)){
135						$flt['logic'] = $logic;
136						$data['filter'][] = $flt;
137					}
138					break;
139				case 'page':
140				case 'target':
141					$data['page'] = cleanID($line[1]);
142					break;
143				default:
144					msg("data plugin: unknown option '".hsc($line[0])."'",-1);
145			}
146		}
147
148		// we need at least one column to display
149		if(!is_array($data['cols']) || !count($data['cols'])){
150			msg('data plugin: no columns selected',-1);
151			return null;
152		}
153
154		// fill up headers with field names if necessary
155		$data['headers'] = (array) $data['headers'];
156		$cnth = count($data['headers']);
157		$cntf = count($data['cols']);
158		for($i=$cnth; $i<$cntf; $i++){
159			$item = array_pop(array_slice($data['cols'],$i,1));
160			$data['headers'][] = $item['title'];
161		}
162
163		$data['sql'] = $this->_buildSQL($data);
164		return $data;
165	}
166
167	protected $before_item = '<tr>';
168	protected $after_item  = '</tr>';
169	protected $before_val  = '<td>';
170	protected $after_val   = '</td>';
171
172	/**
173	 * Create output
174	 */
175	function render($format, &$R, $data) {
176		if($format != 'xhtml') return false;
177		if(is_null($data)) return false;
178		$R->info['cache'] = false;
179
180		$store = $this->dthlp->_getTripleStore();
181		$resultFormat = phpSesame::SPARQL_XML;
182		$lang = "sparql";
183		$infer = true;
184		if(!$store) return false;
185
186		$this->updateSQLwithQuery($data); // handles request params
187
188		// run query
189		$clist = array_keys($data['cols']);
190
191		$res = $store->query($data['sql'],$resultFormat, $lang, $infer);
192
193		if($res->hasRows())
194		{
195			$headers = $res->getHeaders();
196			$R->doc .= $this->preList($clist, $data);
197
198			foreach($res->getRows() as $row)
199			{
200				$R->doc .= $this->before_item;
201				foreach(array_values($headers) as $num => $cval) {
202					$R->doc .= $this->before_val;
203					$values = explode(",",$row[$cval]);
204					sort($values);
205					$R->doc .= $this->dthlp->_formatData(
206					$data['cols'][$clist[$num]],implode(",",$values),$R);
207					$R->doc .= $this->after_val;
208				}
209				$R->doc .= $this->after_item;
210			}
211			$R->doc .= $this->postList($data, $cnt);
212		}
213		else {
214			$this->nullList($data, $clist, $R);
215			return true;
216		}
217
218
219		if ($data['limit'] && $cnt > $data['limit']) {
220			$rows = array_slice($rows, 0, $data['limit']);
221		}
222
223
224		return true;
225	}
226
227	function preList($clist, $data) {
228		global $ID;
229		// build table
230		$text = '<div class="table dataaggregation">'
231		. '<table class="inline dataplugin_table '.$data['classes'].'">';
232		// build column headers
233		$text .= '<tr>';
234		foreach($data['headers'] as $num => $head){
235			$ckey = $clist[$num];
236
237			$text .= '<th>';
238
239			// add sort arrow
240			if(isset($data['sort']) && $ckey == $data['sort'][0]){
241				if($data['sort'][1] == 'ASC'){
242					$text .= '<span>&darr;</span> ';
243					$ckey = '^'.$ckey;
244				}else{
245					$text .= '<span>&uarr;</span> ';
246				}
247			}
248
249			// keep url params
250			$params = $this->dthlp->_a2ua('dataflt',$_REQUEST['dataflt']);
251			$params['datasrt'] = $ckey;
252			$params['dataofs'] = $_REQUEST['dataofs'];
253
254			// clickable header
255			$text .= '<a href="'.wl($ID,$params).
256                       '" title="'.$this->getLang('sort').'">'.hsc($head).'</a>';
257
258			$text .= '</th>';
259		}
260		$text .= '</tr>';
261		return $text;
262	}
263
264	function nullList($data, $clist, &$R) {
265		$R->doc .= $this->preList($clist, $data);
266		$R->tablerow_open();
267		$R->tablecell_open(count($clist), 'center');
268		$R->cdata($this->getLang('none'));
269		$R->tablecell_close();
270		$R->tablerow_close();
271		$R->doc .= '</table></div>';
272	}
273
274	function postList($data, $rowcnt) {
275		global $ID;
276		$text = '';
277		// if limit was set, add control
278		if($data['limit']){
279			$text .= '<tr><th colspan="'.count($data['cols']).'">';
280			$offset = (int) $_REQUEST['dataofs'];
281			if($offset){
282				$prev = $offset - $data['limit'];
283				if($prev < 0) $prev = 0;
284
285				// keep url params
286				$params = $this->dthlp->_a2ua('dataflt',$_REQUEST['dataflt']);
287				$params['datasrt'] = $_REQUEST['datasrt'];
288				$params['dataofs'] = $prev;
289
290				$text .= '<a href="'.wl($ID,$params).
291                              '" title="'.$this->getLang('prev').
292                              '" class="prev">'.$this->getLang('prev').'</a>';
293			}
294
295			$text .= '&nbsp;';
296
297			if($rowcnt > $data['limit']){
298				$next = $offset + $data['limit'];
299
300				// keep url params
301				$params = $this->dthlp->_a2ua('dataflt',$_REQUEST['dataflt']);
302				$params['datasrt'] = $_REQUEST['datasrt'];
303				$params['dataofs'] = $next;
304
305				$text .= '<a href="'.wl($ID,$params).
306                              '" title="'.$this->getLang('next').
307                              '" class="next">'.$this->getLang('next').'</a>';
308			}
309			$text .= '</th></tr>';
310		}
311
312		$text .= '</table></div>';
313		return $text;
314	}
315
316	/**
317	 * Builds the SQL query from the given data
318	 */
319	function _buildSQL(&$data){
320		$cnt    = 0;
321		$tables = array();
322		$select = array();
323		$selectview = array();
324		$wherefirst = '';
325		$where = '';
326		$order  = '';
327
328		// prepare the columns to show
329		foreach ($data['cols'] as &$col){
330			$key = $col['key'];
331			if($key == '%pageid%'){
332				$select[] = '?page';
333				$selectview[] = '?page';
334				$wherefirst .= "{ ?pageurl rdfs:label ?page . }";
335
336			}elseif($key == '%class%'){
337				$select[] = '?class';
338				$selectview[] = '?class';
339				$wherefirst .= "{ ?pageurl spd:class ?class . }";
340			}elseif($key == '%title%'){
341				$select[] = '?title';
342				$selectview[] = '?title';
343				$wherefirst .= "{ ?pageurl spd:title ?title . }";
344			}else{
345				if(!isset($tables[$key])){
346					$tables[$key] = 'T'.(++$cnt);
347					if ($where != '') $where .= " UNION ";
348					$where .= sprintf('{ ?pageurl <%s%s> ?%s . }',$this->getConf('base_url'),urlencode($key),$tables[$key]);
349				}
350				$type = $col['type'];
351				if (is_array($type)) $type = $type['type'];
352
353
354				$select[] = sprintf('(GROUP_CONCAT(DISTINCT ?%s ; SEPARATOR=",") AS ?%ss)',$tables[$key],$tables[$key]);
355				$selectview[] = "?".$tables[$key]."s";
356
357				if ($type=='pageid') $col['type'] = 'title';
358			}
359		}
360		unset($col);
361
362		// prepare sorting
363		if(isset($data['sort'])){
364			$col = $data['sort'][0];
365
366			if($col == '%pageid%'){
367				$order = 'ORDER BY '.$data['sort'][1].'(?page)';
368			}elseif($col == '%class%'){
369				$order = 'ORDER BY '.$data['sort'][1].'(?class)';
370			}elseif($col == '%title%'){
371				$order = 'ORDER BY '.$data['sort'][1].'(?title)';
372			}else{
373				// sort by hidden column?
374				if(!$tables[$col]){
375					$tables[$col] = 'T'.(++$cnt);
376					$select[] = "(GROUP_CONCAT(DISTINCT ?".$tables[$col]." ; SEPARATOR=\",\") AS ?".$tables[$col]."s)";
377					if ($where != '') $where .= " UNION ";
378					$where .= sprintf('{ ?pageurl <%s%s> ?%s . }',$this->getConf('base_url'),urlencode($col),$tables[$col]);
379				}
380				$order = sprintf('ORDER BY %s(?%ss)',$data['sort'][1],$tables[$col]);
381
382			}
383		}else{
384			$order = 'ORDER BY ASC(?page)';
385		}
386
387		// add request filters
388		if (!isset($data['filter'])) $data['filter'] = array();
389		$data['filter'] = array_merge($data['filter'], $this->dthlp->_get_filters());
390
391		// prepare filters
392		if(is_array($data['filter']) && count($data['filter'])){
393			$wherefilter  = '{';
394			foreach($data['filter'] as $filter){
395				$col = $filter['key'];
396
397				if ($filter['logic'] == 'OR') $wherefilter .= '} UNION {';
398
399
400				$predicate = '';
401				if ($col == '%pageid%') $predicate = 'rdfs:label';
402				else {
403					if ($col == '%class%') $predicate = 'spd:class';
404					else {
405						if ($col == '%title%') $predicate = 'spd:title';
406						else {
407							if(!$tables[$col]) $tables[$col] = 'T'.(++$cnt);
408							$predicate = sprintf('<%s%s>',$this->getConf('base_url'),urlencode($col));
409						}
410					}
411				};
412
413				//value is already escaped
414
415				switch ($filter['compare']) {
416					case '=':
417					// seems not necessary, performance impact
418					//	if ($filter['value']=="")
419					//		$wherefilter .= sprintf(' { OPTIONAL { ?pageurl %s ?%s_value . } FILTER (!bound(?%s_value) || str(?%s_value)="") }',$predicate, $col, $col, $col);
420					//	else
421							$wherefilter .= sprintf('{ ?pageurl %s "%s" . }',$predicate, $filter['value']);
422						break;
423					case 'LIKE':
424						$wherefilter .= sprintf('{ ?pageurl %s ?%s_value . FILTER regex(?%s_value,"^%s$") }',$predicate,$col,$col,addslashes(str_replace('%','.*',$filter['value'])));
425						break;
426					case 'NOT LIKE':
427						$wherefilter .= sprintf('{ ?pageurl %s ?%s_value . FILTER (! regex(?%s_value,"^%s$")) }',$predicate,$col,$col,addslashes(str_replace('%','.*',$filter['value'])));
428						break;
429					default:
430						$wherefilter .= sprintf('{ ?pageurl %s ?%s_value . FILTER (?%s_value %s "%s") }',$predicate,$col,$col,$filter['compare'],addslashes($filter['value']));
431				}
432			}
433		}
434		$wherefilter  .= '}';
435
436		// build the query
437
438		$sql =
439        	"PREFIX rdfs:<http://www.w3.org/2000/01/rdf-schema#> ".
440        	sprintf("PREFIX spd:<%s> ",$this->getConf('base_url')).
441			"SELECT ".join(' ',$selectview)." WHERE {".		//keys to display
442        	"SELECT DISTINCT ".join(' ',$select).
443        	"WHERE {".$wherefirst."{".$where."} ".$wherefilter."} GROUP BY ?page ".$order.
444			"}";
445
446
447		// offset and limit
448		if($data['limit']){
449			$sql .= ' LIMIT '.($data['limit'] + 1);
450			// offset is added from REQUEST params in updateSQLwithQuery
451		}
452		return $sql;
453	}
454
455	function updateSQLwithQuery(&$data) {
456		// take overrides from HTTP request params into account
457		if(isset($_REQUEST['datasrt'])){
458			if($_REQUEST['datasrt']{0} == '^'){
459				$data['sort'] = array(substr($_REQUEST['datasrt'],1),'DESC');
460			}else{
461				$data['sort'] = array($_REQUEST['datasrt'],'ASC');
462			}
463			// Rebuild SQL FIXME do this smarter & faster
464			$data['sql'] = $this->_buildSQL($data);
465		}
466
467		if($data['limit'] && (int) $_REQUEST['dataofs']){
468			$data['sql'] .= ' OFFSET '.((int) $_REQUEST['dataofs']);
469        }
470    }
471}
472
473