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