1<?php 2 3namespace dokuwiki\plugin\struct\meta; 4 5use dokuwiki\plugin\struct\types\DateTime; 6use dokuwiki\plugin\struct\types\Decimal; 7use dokuwiki\plugin\struct\types\Page; 8use dokuwiki\plugin\struct\types\AutoSummary; 9use dokuwiki\plugin\struct\types\Text; 10use dokuwiki\plugin\struct\types\User; 11 12class Search { 13 /** 14 * This separator will be used to concat multi values to flatten them in the result set 15 */ 16 const CONCAT_SEPARATOR = "\n!_-_-_-_-_!\n"; 17 18 /** 19 * The list of known and allowed comparators 20 * (order matters) 21 */ 22 static public $COMPARATORS = array( 23 '<=', '>=', '=*', '=', '<', '>', '!=', '!~', '~', 'IN' 24 ); 25 26 /** @var \helper_plugin_sqlite */ 27 protected $sqlite; 28 29 /** @var Schema[] list of schemas to query */ 30 protected $schemas = array(); 31 32 /** @var Column[] list of columns to select */ 33 protected $columns = array(); 34 35 /** @var array the sorting of the result */ 36 protected $sortby = array(); 37 38 /** @var array the filters */ 39 protected $filter = array(); 40 41 /** @var array list of aliases tables can be referenced by */ 42 protected $aliases = array(); 43 44 /** @var int begin results from here */ 45 protected $range_begin = 0; 46 47 /** @var int end results here */ 48 protected $range_end = 0; 49 50 /** @var int the number of results */ 51 protected $count = -1; 52 /** @var string[] the PIDs of the result rows */ 53 protected $result_pids = null; 54 /** @var array the row ids of the result rows */ 55 protected $result_rids = []; 56 /** @var array the revisions of the result rows */ 57 protected $result_revs = []; 58 59 /** 60 * Search constructor. 61 */ 62 public function __construct() { 63 /** @var \helper_plugin_struct_db $plugin */ 64 $plugin = plugin_load('helper', 'struct_db'); 65 $this->sqlite = $plugin->getDB(); 66 } 67 68 /** 69 * Add a schema to be searched 70 * 71 * Call multiple times for multiple schemas. 72 * 73 * @param string $table 74 * @param string $alias 75 */ 76 public function addSchema($table, $alias = '') { 77 $schema = new Schema($table); 78 if(!$schema->getId()) { 79 throw new StructException('schema missing', $table); 80 } 81 82 // FIXME is the mixing still relevant? 83// if($this->schemas && 84// ( 85// $schema->isLookup() || 86// reset($this->schemas)->isLookup() 87// ) 88// ) { 89// throw new StructException('nolookupmix'); 90// } 91 92 $this->schemas[$schema->getTable()] = $schema; 93 if($alias) $this->aliases[$alias] = $schema->getTable(); 94 } 95 96 /** 97 * Add a column to be returned by the search 98 * 99 * Call multiple times for multiple columns. Be sure the referenced tables have been 100 * added before 101 * 102 * @param string $colname may contain an alias 103 */ 104 public function addColumn($colname) { 105 if($this->processWildcard($colname)) return; // wildcard? 106 $col = $this->findColumn($colname); 107 if(!$col) return; //FIXME do we really want to ignore missing columns? 108 $this->columns[] = $col; 109 } 110 111 /** 112 * Add sorting options 113 * 114 * Call multiple times for multiple columns. Be sure the referenced tables have been 115 * added before 116 * 117 * @param string $colname may contain an alias 118 * @param bool $asc sort direction (ASC = true, DESC = false) 119 * @param bool $nc set true for caseinsensitivity 120 */ 121 public function addSort($colname, $asc = true, $nc = true) { 122 $col = $this->findColumn($colname); 123 if(!$col) return; //FIXME do we really want to ignore missing columns? 124 125 $this->sortby[$col->getFullQualifiedLabel()] = array($col, $asc, $nc); 126 } 127 128 /** 129 * Returns all set sort columns 130 * 131 * @return array 132 */ 133 public function getSorts() { 134 return $this->sortby; 135 } 136 137 /** 138 * Adds a filter 139 * 140 * @param string $colname may contain an alias 141 * @param string|string[] $value 142 * @param string $comp @see self::COMPARATORS 143 * @param string $op either 'OR' or 'AND' 144 */ 145 public function addFilter($colname, $value, $comp, $op = 'OR') { 146 /* Convert certain filters into others 147 * this reduces the number of supported filters to implement in types */ 148 if($comp == '*~') { 149 $value = $this->filterWrapAsterisks($value); 150 $comp = '~'; 151 } elseif($comp == '<>') { 152 $comp = '!='; 153 } 154 155 if(!in_array($comp, self::$COMPARATORS)) throw new StructException("Bad comperator. Use " . join(',', self::$COMPARATORS)); 156 if($op != 'OR' && $op != 'AND') throw new StructException('Bad filter type . Only AND or OR allowed'); 157 158 $col = $this->findColumn($colname); 159 if(!$col) return; // ignore missing columns, filter might have been for different schema 160 161 // map filter operators to SQL syntax 162 switch($comp) { 163 case '~': 164 $comp = 'LIKE'; 165 break; 166 case '!~': 167 $comp = 'NOT LIKE'; 168 break; 169 case '=*': 170 $comp = 'REGEXP'; 171 break; 172 } 173 174 // we use asterisks, but SQL wants percents 175 if($comp == 'LIKE' || $comp == 'NOT LIKE') { 176 $value = $this->filterChangeToLike($value); 177 } 178 179 if ($comp == 'IN' && !is_array($value)) { 180 $value = $this->parseFilterValueList($value); 181 //col IN ('a', 'b', 'c') is equal to col = 'a' OR 'col = 'b' OR col = 'c' 182 $comp = '='; 183 } 184 185 // add the filter 186 $this->filter[] = array($col, $value, $comp, $op); 187 } 188 189 /** 190 * Parse SQLite row value into array 191 * 192 * @param string $value 193 * @return string[] 194 */ 195 protected function parseFilterValueList($value) { 196 $Handler = new FilterValueListHandler(); 197 $LexerClass = class_exists('\Doku_Lexer') ? '\Doku_Lexer' : '\dokuwiki\Parsing\Lexer\Lexer'; 198 $isLegacy = $LexerClass === '\Doku_Lexer'; 199 /** @var \Doku_Lexer|\dokuwiki\Parsing\Lexer\Lexer $Lexer */ 200 $Lexer = new $LexerClass($Handler, 'base', true); 201 202 203 $Lexer->addEntryPattern('\(','base','row'); 204 $Lexer->addPattern('\s*,\s*','row'); 205 $Lexer->addExitPattern('\)','row'); 206 207 $Lexer->addEntryPattern('"', 'row', 'double_quote_string'); 208 $Lexer->addSpecialPattern('\\\\"','double_quote_string','escape_sequence'); 209 $Lexer->addExitPattern('"', 'double_quote_string'); 210 211 $Lexer->addEntryPattern("'", 'row', 'single_quote_string'); 212 $Lexer->addSpecialPattern("\\\\'",'single_quote_string','escape_sequence'); 213 $Lexer->addExitPattern("'", 'single_quote_string'); 214 215 $Lexer->mapHandler('double_quote_string','single_quote_string'); 216 217 $Lexer->addSpecialPattern('[-+]?[0-9]*\.?[0-9]+(?:[eE][-+]?[0-9]+)?','row','number'); 218 219 $res = $Lexer->parse($value); 220 221 $currentMode = $isLegacy ? $Lexer->_mode->getCurrent() : $Lexer->getModeStack()->getCurrent(); 222 if (!$res || $currentMode != 'base') { 223 throw new StructException('invalid row value syntax'); 224 } 225 226 return $Handler->get_row(); 227 } 228 229 /** 230 * Wrap given value in asterisks 231 * 232 * @param string|string[] $value 233 * @return string|string[] 234 */ 235 protected function filterWrapAsterisks($value) { 236 $map = function ($input) { 237 return "*$input*"; 238 }; 239 240 if(is_array($value)) { 241 $value = array_map($map, $value); 242 } else { 243 $value = $map($value); 244 } 245 return $value; 246 } 247 248 /** 249 * Change given string to use % instead of * 250 * 251 * @param string|string[] $value 252 * @return string|string[] 253 */ 254 protected function filterChangeToLike($value) { 255 $map = function ($input) { 256 return str_replace('*', '%', $input); 257 }; 258 259 if(is_array($value)) { 260 $value = array_map($map, $value); 261 } else { 262 $value = $map($value); 263 } 264 return $value; 265 } 266 267 /** 268 * Set offset for the results 269 * 270 * @param int $offset 271 */ 272 public function setOffset($offset) { 273 $limit = 0; 274 if($this->range_end) { 275 // if there was a limit set previously, the range_end needs to be recalculated 276 $limit = $this->range_end - $this->range_begin; 277 } 278 $this->range_begin = $offset; 279 if($limit) $this->setLimit($limit); 280 } 281 282 /** 283 * Limit results to this number 284 * 285 * @param int $limit Set to 0 to disable limit again 286 */ 287 public function setLimit($limit) { 288 if($limit) { 289 $this->range_end = $this->range_begin + $limit; 290 } else { 291 $this->range_end = 0; 292 } 293 } 294 295 /** 296 * Return the number of results (regardless of limit and offset settings) 297 * 298 * Use this to implement paging. Important: this may only be called after running @see execute() 299 * 300 * @return int 301 */ 302 public function getCount() { 303 if($this->count < 0) throw new StructException('Count is only accessible after executing the search'); 304 return $this->count; 305 } 306 307 /** 308 * Returns the PID associated with each result row 309 * 310 * Important: this may only be called after running @see execute() 311 * 312 * @return \string[] 313 */ 314 public function getPids() { 315 if($this->result_pids === null) throw new StructException('PIDs are only accessible after executing the search'); 316 return $this->result_pids; 317 } 318 319 /** 320 * Returns the rid associated with each result row 321 * 322 * Important: this may only be called after running @see execute() 323 * 324 * @return array 325 */ 326 public function getRids() { 327 if($this->result_rids === null) throw new StructException('rids are only accessible after executing the search'); 328 return $this->result_rids; 329 } 330 331 /** 332 * Returns the rid associated with each result row 333 * 334 * Important: this may only be called after running @see execute() 335 * 336 * @return array 337 */ 338 public function getRevs() { 339 if($this->result_revs === null) throw new StructException('revs are only accessible after executing the search'); 340 return $this->result_revs; 341 } 342 343 /** 344 * Execute this search and return the result 345 * 346 * The result is a two dimensional array of Value()s. 347 * 348 * This will always query for the full result (not using offset and limit) and then 349 * return the wanted range, setting the count (@see getCount) to the whole result number 350 * 351 * @param string $idColumn Column on which to join tables 352 * @return Value[][] 353 */ 354 public function execute($idColumn = 'rid') { 355 list($sql, $opts) = $this->getSQL($idColumn); 356 357 /** @var \PDOStatement $res */ 358 $res = $this->sqlite->query($sql, $opts); 359 if($res === false) throw new StructException("SQL execution failed for\n\n$sql"); 360 361 $this->result_pids = array(); 362 $result = array(); 363 $cursor = -1; 364 $pageidAndRevOnly = array_reduce($this->columns, function ($pageidAndRevOnly, Column $col) { 365 return $pageidAndRevOnly && ($col->getTid() == 0); 366 }, true); 367 while($row = $res->fetch(\PDO::FETCH_ASSOC)) { 368 $cursor++; 369 if($cursor < $this->range_begin) continue; 370 if($this->range_end && $cursor >= $this->range_end) continue; 371 372 $C = 0; 373 $resrow = array(); 374 $isempty = true; 375 foreach($this->columns as $col) { 376 $val = $row["C$C"]; 377 if($col->isMulti()) { 378 $val = explode(self::CONCAT_SEPARATOR, $val); 379 } 380 $value = new Value($col, $val); 381 $isempty &= $this->isEmptyValue($value); 382 $resrow[] = $value; 383 $C++; 384 } 385 386 // skip empty rows 387 if($isempty && !$pageidAndRevOnly) { 388 $cursor--; 389 continue; 390 } 391 392 $this->result_pids[] = $row['PID']; 393 $this->result_rids[] = $row['rid']; 394 $this->result_revs[] = $row['rev']; 395 $result[] = $resrow; 396 } 397 398 $this->sqlite->res_close($res); 399 $this->count = $cursor + 1; 400 return $result; 401 } 402 403 /** 404 * Transform the set search parameters into a statement 405 * 406 * @param string $idColumn Column on which to join tables 407 * @return array ($sql, $opts) The SQL and parameters to execute 408 */ 409 public function getSQL($idColumn) { 410 if(!$this->columns) throw new StructException('nocolname'); 411 412 $QB = new QueryBuilder(); 413 414 // basic tables 415 $first_table = ''; 416 foreach($this->schemas as $schema) { 417 $datatable = 'data_' . $schema->getTable(); 418 if($first_table) { 419 // follow up tables 420 $QB->addLeftJoin($first_table, $datatable, $datatable, "$first_table.$idColumn = $datatable.$idColumn"); 421 } else { 422 // first table 423 if($idColumn === 'pid') { 424 $QB->addTable('schema_assignments'); 425 $QB->filters()->whereAnd("$datatable.pid = schema_assignments.pid"); 426 $QB->filters()->whereAnd("schema_assignments.tbl = '{$schema->getTable()}'"); 427 $QB->filters()->whereAnd("schema_assignments.assigned = 1"); 428 $QB->filters()->whereAnd("GETACCESSLEVEL($datatable.pid) > 0"); 429 $QB->filters()->whereAnd("PAGEEXISTS($datatable.pid) = 1"); 430 } 431 432 $QB->addTable($datatable); 433 $QB->addSelectColumn($datatable, 'rid'); 434 $QB->addSelectColumn($datatable, 'pid', 'PID'); 435 $QB->addSelectColumn($datatable, 'rev'); 436 $QB->addGroupByColumn($datatable, 'pid'); 437 438 $first_table = $datatable; 439 } 440 $QB->filters()->whereAnd("$datatable.latest = 1"); 441 } 442 443 // columns to select, handling multis 444 $sep = self::CONCAT_SEPARATOR; 445 $n = 0; 446 foreach($this->columns as $col) { 447 $CN = 'C' . $n++; 448 449 if($col->isMulti()) { 450 $datatable = "data_{$col->getTable()}"; 451 $multitable = "multi_{$col->getTable()}"; 452 $MN = $QB->generateTableAlias('M'); 453 454 $QB->addLeftJoin( 455 $datatable, 456 $multitable, 457 $MN, 458 "$datatable.$idColumn = $MN.$idColumn AND 459 $datatable.rev = $MN.rev AND 460 $MN.colref = {$col->getColref()}" 461 ); 462 463 $col->getType()->select($QB, $MN, 'value', $CN); 464 $sel = $QB->getSelectStatement($CN); 465 $QB->addSelectStatement("GROUP_CONCAT($sel, '$sep')", $CN); 466 } else { 467 $col->getType()->select($QB, 'data_' . $col->getTable(), $col->getColName(), $CN); 468 $QB->addGroupByStatement($CN); 469 } 470 } 471 472 // where clauses 473 if (!empty($this->filter)) { 474 $userWHERE = $QB->filters()->where('AND'); 475 } 476 foreach($this->filter as $filter) { 477 /** @var Column $col */ 478 list($col, $value, $comp, $op) = $filter; 479 480 $datatable = "data_{$col->getTable()}"; 481 $multitable = "multi_{$col->getTable()}"; 482 483 /** @var $col Column */ 484 if($col->isMulti()) { 485 $MN = $QB->generateTableAlias('MN'); 486 487 $QB->addLeftJoin( 488 $datatable, 489 $multitable, 490 $MN, 491 "$datatable.$idColumn = $MN.$idColumn AND 492 $datatable.rev = $MN.rev AND 493 $MN.colref = {$col->getColref()}" 494 ); 495 $coltbl = $MN; 496 $colnam = 'value'; 497 } else { 498 $coltbl = $datatable; 499 $colnam = $col->getColName(); 500 } 501 502 $col->getType()->filter($userWHERE, $coltbl, $colnam, $comp, $value, $op); // type based filter 503 } 504 505 // sorting - we always sort by the single val column 506 foreach($this->sortby as $sort) { 507 list($col, $asc, $nc) = $sort; 508 /** @var $col Column */ 509 $colname = $col->getColName(false); 510 if($nc) $colname .= ' COLLATE NOCASE'; 511 $col->getType()->sort($QB, 'data_' . $col->getTable(), $colname, $asc ? 'ASC' : 'DESC'); 512 } 513 514 return $QB->getSQL(); 515 } 516 517 /** 518 * Returns all the columns that where added to the search 519 * 520 * @return Column[] 521 */ 522 public function getColumns() { 523 return $this->columns; 524 } 525 526 /** 527 * All the schemas currently added 528 * 529 * @return Schema[] 530 */ 531 public function getSchemas() { 532 return array_values($this->schemas); 533 } 534 535 /** 536 * Checks if the given column is a * wildcard 537 * 538 * If it's a wildcard all matching columns are added to the column list, otherwise 539 * nothing happens 540 * 541 * @param string $colname 542 * @return bool was wildcard? 543 */ 544 protected function processWildcard($colname) { 545 list($colname, $table) = $this->resolveColumn($colname); 546 if($colname !== '*') return false; 547 548 // no table given? assume the first is meant 549 if($table === null) { 550 $schema_list = array_keys($this->schemas); 551 $table = $schema_list[0]; 552 } 553 554 $schema = $this->schemas[$table]; 555 if(!$schema) return false; 556 $this->columns = array_merge($this->columns, $schema->getColumns(false)); 557 return true; 558 } 559 560 /** 561 * Split a given column name into table and column 562 * 563 * Handles Aliases. Table might be null if none given. 564 * 565 * @param $colname 566 * @return array (colname, table) 567 */ 568 protected function resolveColumn($colname) { 569 if(!$this->schemas) throw new StructException('noschemas'); 570 571 // resolve the alias or table name 572 @list($table, $colname) = explode('.', $colname, 2); 573 if(!$colname) { 574 $colname = $table; 575 $table = null; 576 } 577 if($table && isset($this->aliases[$table])) { 578 $table = $this->aliases[$table]; 579 } 580 581 if(!$colname) throw new StructException('nocolname'); 582 583 return array($colname, $table); 584 } 585 586 /** 587 * Find a column to be used in the search 588 * 589 * @param string $colname may contain an alias 590 * @return bool|Column 591 */ 592 public function findColumn($colname) { 593 if(!$this->schemas) throw new StructException('noschemas'); 594 $schema_list = array_keys($this->schemas); 595 596 // add "fake" column for special col 597 if($colname == '%pageid%') { 598 return new PageColumn(0, new Page(), $schema_list[0]); 599 } 600 if($colname == '%title%') { 601 return new PageColumn(0, new Page(array('usetitles' => true)), $schema_list[0]); 602 } 603 if($colname == '%lastupdate%') { 604 return new RevisionColumn(0, new DateTime(), $schema_list[0]); 605 } 606 if ($colname == '%lasteditor%') { 607 return new UserColumn(0, new User(), $schema_list[0]); 608 } 609 if ($colname == '%lastsummary%') { 610 return new SummaryColumn(0, new AutoSummary(), $schema_list[0]); 611 } 612 if($colname == '%rowid%') { 613 return new RowColumn(0, new Decimal(), $schema_list[0]); 614 } 615 616 list($colname, $table) = $this->resolveColumn($colname); 617 618 // if table name given search only that, otherwise try all for matching column name 619 if($table !== null) { 620 $schemas = array($table => $this->schemas[$table]); 621 } else { 622 $schemas = $this->schemas; 623 } 624 625 // find it 626 $col = false; 627 foreach($schemas as $schema) { 628 if(empty($schema)) { 629 continue; 630 } 631 $col = $schema->findColumn($colname); 632 if($col) break; 633 } 634 635 return $col; 636 } 637 638 /** 639 * Check if the given row is empty or references our own row 640 * 641 * @param Value $value 642 * @return bool 643 */ 644 protected function isEmptyValue(Value $value) { 645 if ($value->isEmpty()) return true; 646 if ($value->getColumn()->getTid() == 0) return true; 647 return false; 648 } 649} 650 651 652