xref: /plugin/struct/meta/Search.php (revision e8e4f25a9fa8bb076e767cb0732dc15ad6aaa5ad) 
1<?php
2
3namespace plugin\struct\meta;
4
5use Exception;
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    /** @var  \helper_plugin_sqlite */
14    protected $sqlite;
15
16    /** @var Schema[] list of schemas to query */
17    protected $schemas = array();
18
19    /** @var Column[] list of columns to select */
20    protected $columns = array();
21
22    /** @var array the sorting of the result */
23    protected $sortby = array();
24
25    /** @var array the filters */
26    protected $filter = array();
27
28    /** @var array list of aliases tables can be referenced by */
29    protected $aliases = array();
30
31    /**
32     * Search constructor.
33     */
34    public function __construct() {
35        /** @var \helper_plugin_struct_db $plugin */
36        $plugin = plugin_load('helper', 'struct_db');
37        $this->sqlite = $plugin->getDB();
38    }
39
40    /**
41     * Add a schema to be searched
42     *
43     * Call multiple times for multiple schemas.
44     *
45     * @param string $table
46     * @param string $alias
47     */
48    public function addSchema($table, $alias = '') {
49        $this->schemas[$table] = new Schema($table);
50        if($alias) $this->aliases[$alias] = $table;
51    }
52
53    /**
54     * Add a column to be returned by the search
55     *
56     * Call multiple times for multiple columns. Be sure the referenced tables have been
57     * added before
58     *
59     * @param string $colname may contain an alias
60     */
61    public function addColumn($colname) {
62        $col = $this->findColumn($colname);
63        if(!$col) return; //FIXME do we really want to ignore missing columns?
64        $this->columns[] = $col;
65    }
66
67    /**
68     * Add sorting options
69     *
70     * Call multiple times for multiple columns. Be sure the referenced tables have been
71     * added before
72     *
73     * @param string $colname may contain an alias
74     * @param bool $asc sort direction (ASC = true, DESC = false)
75     */
76    public function addSort($colname, $asc = true) {
77        $col = $this->findColumn($colname);
78        if(!$col) return; //FIXME do we really want to ignore missing columns?
79
80        $this->sortby[] = array($col, $asc);
81    }
82
83    /**
84     * Adds a filter
85     *
86     * @param string $colname may contain an alias
87     * @param string $value
88     * @param string $comp ('=', '<', '>', '<=', '>=', '~')
89     * @param string $type either 'OR' or 'AND'
90     */
91    public function addFilter($colname, $value, $comp, $type = 'OR') {
92        if(!in_array($comp, array('<', '>', '<=', '>=', '~'))) throw new SearchException("Bad comperator. Use '=', '<', '>', '<=', '>=' or '~'");
93        if($type != 'OR' && $type != 'AND') throw new SearchException('Bad filter type . Only AND or OR allowed');
94
95        $col = $this->findColumn($colname);
96        if(!$col) return; //FIXME do we really want to ignore missing columns?
97
98        $this->filter[] = array($col, $value, $comp, $type);
99    }
100
101    /**
102     * Transform the set search parameters into a statement
103     *
104     * @todo limit to the newest data!
105     * @return string
106     */
107    public function getSQL() {
108        if(!$this->columns) throw new SearchException('nocolname');
109
110        $from = '';
111        $select = '';
112        $order = '';
113        $grouping = array();
114        $opts = array();
115        $where = '1 = 1';
116
117        // basic tables
118        $first = '';
119        foreach($this->schemas as $schema) {
120            if($first) {
121                // follow up tables
122                $from .= "\nLEFT OUTER JOIN data_{$schema->getTable()} ON data_$first.pid = data_{$schema->getTable()}.pid";
123            } else {
124                // first table
125                $select .= "data_{$schema->getTable()}.pid as PID, ";
126                $from .= "data_{$schema->getTable()}";
127                $first = $schema->getTable();
128            }
129        }
130
131        // columns to select, handling multis
132        $sep = self::CONCAT_SEPARATOR;
133        $n = 0;
134        foreach($this->columns as $col) {
135            $CN = 'C' . $n++;
136
137            if($col->isMulti()) {
138                $tn = 'M' . $col->getColref();
139                $select .= "GROUP_CONCAT($tn.value, '$sep') AS $CN, ";
140                $from .= "\nLEFT OUTER JOIN multivals AS $tn";
141                $from .= " ON data_{$col->getTable()}.pid = $tn.pid AND data_{$col->getTable()}.rev = $tn.rev";
142                $from .= " AND $tn.tbl = '{$col->getTable()}' AND $tn.colref = {$col->getColref()}\n";
143            } else {
144                $select .= 'data_' . $col->getTable() . ' . col' . $col->getColref() . " AS $CN, ";
145                $grouping[] = $CN;
146            }
147        }
148        $select = rtrim($select, ', ');
149
150        // where clauses
151        foreach($this->filter as $filter) {
152            list($col, $value, $comp, $type) = $filter;
153
154            /** @var $col Column */
155            if($col->isMulti()) {
156                $tn = 'MN' . $col->getColref(); // FIXME this joins a second time if the column was selected before
157                $from .= "\nLEFT OUTER JOIN multivals AS $tn";
158                $from .= " ON data_{$col->getTable()}.pid = $tn.pid AND data_{$col->getTable()}.rev = $tn.rev";
159                $from .= " AND $tn.tbl = '{$col->getTable()}' AND $tn.colref = {$col->getColref()}\n";
160
161                $column = "$tn.value";
162            } else {
163                $column = "data_{$col->getTable()}.col{$col->getColref()}";
164            }
165
166            list($wsql, $wopt) = $col->getType()->compare($column, $comp, $value);
167            $opts = array_merge($opts, $wopt);
168
169            $where .= " $type $wsql";
170        }
171
172        // sorting
173        foreach($this->sortby as $sort) {
174            list($col, $asc) = $sort;
175
176            /** @var $col Column */
177            if($col->isMulti()) {
178                // FIXME how to sort by multival?
179                // FIXME what if sort by non merged multival?
180            } else {
181                $order .= "data_{$col->getTable()}.col{$col->getColref()} ";
182                $order .= ($asc) ? 'ASC' : 'DESC';
183                $order .= ', ';
184            }
185        }
186        $order = rtrim($order, ', ');
187
188        $sql = "SELECT $select\n  FROM $from\nWHERE $where\nGROUP BY " . join(', ', $grouping);
189        if($order) $sql .= "\nORDER BY $order";
190
191        {#debugging
192            $res = $this->sqlite->query($sql, $opts);
193            $data = $this->sqlite->res2arr($res);
194            $this->sqlite->res_close($res);
195            print_r($data);
196        }
197
198        return $sql;
199    }
200
201    /**
202     * Find a column to be used in the search
203     *
204     * @param string $colname may contain an alias
205     * @return bool|Column
206     */
207    protected function findColumn($colname) {
208        if(!$this->schemas) throw new SearchException('noschemas');
209
210        // resolve the alias or table name
211        list($table, $colname) = explode('.', $colname, 2);
212        if(!$colname) {
213            $colname = $table;
214            $table = '';
215        }
216        if($table && isset($this->aliases[$table])) {
217            $table = $this->aliases[$table];
218        }
219
220        if(!$colname) throw new SearchException('nocolname');
221
222        // if table name given search only that, otherwiese try all for matching column name
223        if($table) {
224            $schemas = array($table => $this->schemas[$table]);
225        } else {
226            $schemas = $this->schemas;
227        }
228
229        // find it
230        $col = false;
231        foreach($schemas as $schema) {
232            $col = $schema->findColumn($colname);
233            if($col) break;
234        }
235
236        return $col;
237    }
238
239}
240
241/**
242 * Class SearchException
243 *
244 * A translatable exception
245 *
246 * @package plugin\struct\meta
247 */
248class SearchException extends \RuntimeException {
249    public function __construct($message, $code = -1, Exception $previous = null) {
250        /** @var \action_plugin_struct_autoloader $plugin */
251        $plugin = plugin_load('action', 'struct_autoloader');
252        $trans = $plugin->getLang('searchex_' . $message);
253        if(!$trans) $trans = $message;
254        parent::__construct($trans, $code, $previous);
255    }
256}
257