xref: /plugin/struct/meta/Search.php (revision 7059e7e1a02cfabd1f398aeb25ae4b1db66c5b4d) 
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            $where .= "\nAND data_{$schema->getTable()}.latest = 1";
131        }
132
133        // columns to select, handling multis
134        $sep = self::CONCAT_SEPARATOR;
135        $n = 0;
136        foreach($this->columns as $col) {
137            $CN = 'C' . $n++;
138
139            if($col->isMulti()) {
140                $tn = 'M' . $col->getColref();
141                $select .= "GROUP_CONCAT($tn.value, '$sep') AS $CN, ";
142                $from .= "\nLEFT OUTER JOIN multivals AS $tn";
143                $from .= " ON data_{$col->getTable()}.pid = $tn.pid AND data_{$col->getTable()}.rev = $tn.rev";
144                $from .= " AND $tn.tbl = '{$col->getTable()}' AND $tn.colref = {$col->getColref()}\n";
145            } else {
146                $select .= 'data_' . $col->getTable() . ' . col' . $col->getColref() . " AS $CN, ";
147                $grouping[] = $CN;
148            }
149        }
150        $select = rtrim($select, ', ');
151
152        // where clauses
153        foreach($this->filter as $filter) {
154            list($col, $value, $comp, $type) = $filter;
155
156            /** @var $col Column */
157            if($col->isMulti()) {
158                $tn = 'MN' . $col->getColref(); // FIXME this joins a second time if the column was selected before
159                $from .= "\nLEFT OUTER JOIN multivals AS $tn";
160                $from .= " ON data_{$col->getTable()}.pid = $tn.pid AND data_{$col->getTable()}.rev = $tn.rev";
161                $from .= " AND $tn.tbl = '{$col->getTable()}' AND $tn.colref = {$col->getColref()}\n";
162
163                $column = "$tn.value";
164            } else {
165                $column = "data_{$col->getTable()}.col{$col->getColref()}";
166            }
167
168            list($wsql, $wopt) = $col->getType()->compare($column, $comp, $value);
169            $opts = array_merge($opts, $wopt);
170
171            $where .= " $type $wsql";
172        }
173
174        // sorting
175        foreach($this->sortby as $sort) {
176            list($col, $asc) = $sort;
177
178            /** @var $col Column */
179            if($col->isMulti()) {
180                // FIXME how to sort by multival?
181                // FIXME what if sort by non merged multival?
182            } else {
183                $order .= "data_{$col->getTable()}.col{$col->getColref()} ";
184                $order .= ($asc) ? 'ASC' : 'DESC';
185                $order .= ', ';
186            }
187        }
188        $order = rtrim($order, ', ');
189
190        $sql = "SELECT $select\n  FROM $from\nWHERE $where\nGROUP BY " . join(', ', $grouping);
191        if($order) $sql .= "\nORDER BY $order";
192
193        {#debugging
194            $res = $this->sqlite->query($sql, $opts);
195            $data = $this->sqlite->res2arr($res);
196            $this->sqlite->res_close($res);
197            print_r($data);
198        }
199
200        return $sql;
201    }
202
203    /**
204     * Find a column to be used in the search
205     *
206     * @param string $colname may contain an alias
207     * @return bool|Column
208     */
209    protected function findColumn($colname) {
210        if(!$this->schemas) throw new SearchException('noschemas');
211
212        // resolve the alias or table name
213        list($table, $colname) = explode('.', $colname, 2);
214        if(!$colname) {
215            $colname = $table;
216            $table = '';
217        }
218        if($table && isset($this->aliases[$table])) {
219            $table = $this->aliases[$table];
220        }
221
222        if(!$colname) throw new SearchException('nocolname');
223
224        // if table name given search only that, otherwiese try all for matching column name
225        if($table) {
226            $schemas = array($table => $this->schemas[$table]);
227        } else {
228            $schemas = $this->schemas;
229        }
230
231        // find it
232        $col = false;
233        foreach($schemas as $schema) {
234            $col = $schema->findColumn($colname);
235            if($col) break;
236        }
237
238        return $col;
239    }
240
241}
242
243/**
244 * Class SearchException
245 *
246 * A translatable exception
247 *
248 * @package plugin\struct\meta
249 */
250class SearchException extends \RuntimeException {
251    public function __construct($message, $code = -1, Exception $previous = null) {
252        /** @var \action_plugin_struct_autoloader $plugin */
253        $plugin = plugin_load('action', 'struct_autoloader');
254        $trans = $plugin->getLang('searchex_' . $message);
255        if(!$trans) $trans = $message;
256        parent::__construct($trans, $code, $previous);
257    }
258}
259