xref: /plugin/struct/meta/Search.php (revision 49d385736e9d9750c47001456827fe8af155ff49)
1<?php
2
3namespace plugin\struct\meta;
4
5use plugin\struct\types\Page;
6
7class Search {
8    /**
9     * This separator will be used to concat multi values to flatten them in the result set
10     */
11    const CONCAT_SEPARATOR = "\n!_-_-_-_-_!\n";
12
13    /**
14     * The list of known and allowed comparators
15     */
16    static public $COMPARATORS = array(
17        '<=', '>=', '=', '<', '>', '!=', '!~', '~'
18    );
19
20    /** @var  \helper_plugin_sqlite */
21    protected $sqlite;
22
23    /** @var Schema[] list of schemas to query */
24    protected $schemas = array();
25
26    /** @var Column[] list of columns to select */
27    protected $columns = array();
28
29    /** @var array the sorting of the result */
30    protected $sortby = array();
31
32    /** @var array the filters */
33    protected $filter = array();
34
35    /** @var array list of aliases tables can be referenced by */
36    protected $aliases = array();
37
38    /** @var  int begin results from here */
39    protected $range_begin = 0;
40
41    /** @var  int end results here */
42    protected $range_end = 0;
43
44    /** @var int the number of results */
45    protected $count = -1;
46
47    /**
48     * Search constructor.
49     */
50    public function __construct() {
51        /** @var \helper_plugin_struct_db $plugin */
52        $plugin = plugin_load('helper', 'struct_db');
53        $this->sqlite = $plugin->getDB();
54    }
55
56    /**
57     * Add a schema to be searched
58     *
59     * Call multiple times for multiple schemas.
60     *
61     * @param string $table
62     * @param string $alias
63     */
64    public function addSchema($table, $alias = '') {
65        $this->schemas[$table] = new Schema($table);
66        if($alias) $this->aliases[$alias] = $table;
67    }
68
69    /**
70     * Add a column to be returned by the search
71     *
72     * Call multiple times for multiple columns. Be sure the referenced tables have been
73     * added before
74     *
75     * @param string $colname may contain an alias
76     */
77    public function addColumn($colname) {
78        $col = $this->findColumn($colname);
79        if(!$col) return; //FIXME do we really want to ignore missing columns?
80        $this->columns[] = $col;
81    }
82
83    /**
84     * Add sorting options
85     *
86     * Call multiple times for multiple columns. Be sure the referenced tables have been
87     * added before
88     *
89     * @param string $colname may contain an alias
90     * @param bool $asc sort direction (ASC = true, DESC = false)
91     */
92    public function addSort($colname, $asc = true) {
93        $col = $this->findColumn($colname);
94        if(!$col) return; //FIXME do we really want to ignore missing columns?
95
96        $this->sortby[] = array($col, $asc);
97    }
98
99    /**
100     * Adds a filter
101     *
102     * @param string $colname may contain an alias
103     * @param string $value
104     * @param string $comp @see self::COMPARATORS
105     * @param string $type either 'OR' or 'AND'
106     */
107    public function addFilter($colname, $value, $comp, $type = 'OR') {
108        if(!in_array($comp, self::$COMPARATORS)) throw new StructException("Bad comperator. Use " . join(',', self::$COMPARATORS));
109        if($type != 'OR' && $type != 'AND') throw new StructException('Bad filter type . Only AND or OR allowed');
110
111        $col = $this->findColumn($colname);
112        if(!$col) return; //FIXME do we really want to ignore missing columns?
113        $value = str_replace('*','%',$value);
114        $this->filter[] = array($col, $value, $comp, $type);
115    }
116
117    /**
118     * Set offset for the results
119     *
120     * @param int $offset
121     */
122    public function setOffset($offset) {
123        $limit = 0;
124        if($this->range_end) {
125            // if there was a limit set previously, the range_end needs to be recalculated
126            $limit = $this->range_end - $this->range_begin;
127        }
128        $this->range_begin = $offset;
129        if($limit) $this->setLimit($limit);
130    }
131
132    /**
133     * Limit results to this number
134     *
135     * @param int $limit Set to 0 to disable limit again
136     */
137    public function setLimit($limit) {
138        if($limit) {
139            $this->range_end = $this->range_begin + $limit;
140        } else {
141            $this->range_end = 0;
142        }
143    }
144
145    /**
146     * Return the number of results (regardless of limit and offset settings)
147     *
148     * Use this to implement paging. Important: this may only be called after running @see execute()
149     *
150     * @return int
151     */
152    public function getCount() {
153        if($this->count < 0) throw new StructException('Count is only accessible after executing the search');
154        return $this->count;
155    }
156
157    /**
158     * Execute this search and return the result
159     *
160     * The result is a two dimensional array of Value()s.
161     *
162     * This will always query for the full result (not using offset and limit) and then
163     * return the wanted range, setting the count (@see getCount) to the whole result number
164     *
165     * @return Value[][]
166     */
167    public function execute() {
168        list($sql, $opts) = $this->getSQL();
169
170        /** @var \PDOStatement $res */
171        $res = $this->sqlite->query($sql, $opts);
172
173        $result = array();
174        $cursor = -1;
175        while($row = $res->fetch(\PDO::FETCH_ASSOC)) {
176            $cursor++;
177            if($cursor < $this->range_begin) continue;
178            if($this->range_end && $cursor >= $this->range_end) continue;
179
180            $C = 0;
181            $resrow = array();
182            foreach($this->columns as $col) {
183                $val = $row["C$C"];
184                if($col->isMulti()) {
185                    $val = explode(self::CONCAT_SEPARATOR, $val);
186                }
187                $resrow[] = new Value($col, $val);
188                $C++;
189            }
190            $result[] = $resrow;
191        }
192
193        $this->sqlite->res_close($res);
194        $this->count = $cursor + 1;
195        return $result;
196    }
197
198    /**
199     * Transform the set search parameters into a statement
200     *
201     * @return array ($sql, $opts) The SQL and parameters to execute
202     */
203    public function getSQL() {
204        if(!$this->columns) throw new StructException('nocolname');
205
206        $from = '';
207        $select = '';
208        $order = '';
209        $grouping = array();
210        $opts = array();
211        $where = '1 = 1';
212
213        // basic tables
214        $first = '';
215        foreach($this->schemas as $schema) {
216            if($first) {
217                // follow up tables
218                $from .= "\nLEFT OUTER JOIN data_{$schema->getTable()} ON data_$first.pid = data_{$schema->getTable()}.pid";
219            } else {
220                // first table
221                $select .= "data_{$schema->getTable()}.pid as PID, ";
222                $from .= "data_{$schema->getTable()}";
223                $where .= "\nAND GETACCESSLEVEL(data_{$schema->getTable()}.pid) > 0";
224                $first = $schema->getTable();
225            }
226
227            $where .= "\nAND data_{$schema->getTable()}.latest = 1";
228        }
229
230        // columns to select, handling multis
231        $sep = self::CONCAT_SEPARATOR;
232        $n = 0;
233        foreach($this->columns as $col) {
234            $CN = 'C' . $n++;
235
236            if($col->isMulti()) {
237                $tn = 'M' . $col->getColref();
238                $select .= "GROUP_CONCAT($tn.value, '$sep') AS $CN, ";
239                $from .= "\nLEFT OUTER JOIN multi_{$col->getTable()} AS $tn";
240                $from .= " ON data_{$col->getTable()}.pid = $tn.pid AND data_{$col->getTable()}.rev = $tn.rev";
241                $from .= " AND $tn.colref = {$col->getColref()}\n";
242            } else {
243                $select .= "{$col->getColName()} AS $CN, ";
244                $grouping[] = $CN;
245            }
246        }
247        $select = rtrim($select, ', ');
248
249        // where clauses
250        foreach($this->filter as $filter) {
251            list($col, $value, $comp, $type) = $filter;
252
253            /** @var $col Column */
254            if($col->isMulti()) {
255                $tn = 'MN' . $col->getColref(); // FIXME this joins a second time if the column was selected before
256                $from .= "\nLEFT OUTER JOIN multi_{$col->getTable()} AS $tn";
257                $from .= " ON data_{$col->getTable()}.pid = $tn.pid AND data_{$col->getTable()}.rev = $tn.rev";
258                $from .= " AND $tn.colref = {$col->getColref()}\n";
259
260                $column = "$tn.value";
261            } else {
262                $column = $col->getColName();
263            }
264
265            list($wsql, $wopt) = $col->getType()->compare($column, $comp, $value);
266            $opts = array_merge($opts, $wopt);
267
268            $where .= "\n$type $wsql";
269        }
270
271        // sorting
272        foreach($this->sortby as $sort) {
273            list($col, $asc) = $sort;
274
275            /** @var $col Column */
276            if($col->isMulti()) {
277                // FIXME how to sort by multival?
278                // FIXME what if sort by non merged multival?
279            } else {
280                $order .= $col->getColName() . ' ';
281                $order .= ($asc) ? 'ASC' : 'DESC';
282                $order .= ', ';
283            }
284        }
285        $order = rtrim($order, ', ');
286
287        $sql = "SELECT $select\n  FROM $from\nWHERE $where\nGROUP BY " . join(', ', $grouping);
288        if($order) $sql .= "\nORDER BY $order";
289
290        return array($sql, $opts);
291    }
292
293    /**
294     * Returns all the columns that where added to the search
295     *
296     * @return Column[]
297     */
298    public function getColumns() {
299        return $this->columns;
300    }
301
302
303    /**
304     * Find a column to be used in the search
305     *
306     * @param string $colname may contain an alias
307     * @return bool|Column
308     */
309    protected function findColumn($colname) {
310        if(!$this->schemas) throw new StructException('noschemas');
311
312        // handling of page column is special
313        if($colname == '%pageid%') {
314            $schema_list = array_keys($this->schemas);
315            return new PageColumn(0, new Page(), array_shift($schema_list));
316        }
317        // FIXME %title% needs to be handled here, too (later)
318
319        // resolve the alias or table name
320        list($table, $colname) = explode('.', $colname, 2);
321        if(!$colname) {
322            $colname = $table;
323            $table = '';
324        }
325        if($table && isset($this->aliases[$table])) {
326            $table = $this->aliases[$table];
327        }
328
329        if(!$colname) throw new StructException('nocolname');
330
331        // if table name given search only that, otherwiese try all for matching column name
332        if($table) {
333            $schemas = array($table => $this->schemas[$table]);
334        } else {
335            $schemas = $this->schemas;
336        }
337
338        // find it
339        $col = false;
340        foreach($schemas as $schema) {
341            $col = $schema->findColumn($colname);
342            if($col) break;
343        }
344
345        return $col;
346    }
347
348}
349
350
351