1<?php
2// This file is part of Moodle - http://moodle.org/
3//
4// Moodle is free software: you can redistribute it and/or modify
5// it under the terms of the GNU General Public License as published by
6// the Free Software Foundation, either version 3 of the License, or
7// (at your option) any later version.
8//
9// Moodle is distributed in the hope that it will be useful,
10// but WITHOUT ANY WARRANTY; without even the implied warranty of
11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
12// GNU General Public License for more details.
13//
14// You should have received a copy of the GNU General Public License
15// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
16
17/**
18 * Simple moodle database engine.
19 *
20 * @package    search_simpledb
21 * @copyright  2016 David Monllao {@link http://www.davidmonllao.com}
22 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
23 */
24
25namespace search_simpledb;
26
27defined('MOODLE_INTERNAL') || die();
28
29/**
30 * Simple moodle database engine.
31 *
32 * @package    search_simpledb
33 * @copyright  2016 David Monllao {@link http://www.davidmonllao.com}
34 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
35 */
36class engine extends \core_search\engine {
37
38    /**
39     * Total number of available results.
40     *
41     * @var null|int
42     */
43    protected $totalresults = null;
44
45    /**
46     * Prepares a SQL query, applies filters and executes it returning its results.
47     *
48     * @throws \core_search\engine_exception
49     * @param  stdClass     $filters Containing query and filters.
50     * @param  array        $usercontexts Contexts where the user has access. True if the user can access all contexts.
51     * @param  int          $limit The maximum number of results to return.
52     * @return \core_search\document[] Results or false if no results
53     */
54    public function execute_query($filters, $usercontexts, $limit = 0) {
55        global $DB, $USER;
56
57        $serverstatus = $this->is_server_ready();
58        if ($serverstatus !== true) {
59            throw new \core_search\engine_exception('engineserverstatus', 'search');
60        }
61
62        if (empty($limit)) {
63            $limit = \core_search\manager::MAX_RESULTS;
64        }
65
66        $params = array();
67
68        // To store all conditions we will add to where.
69        $ands = array();
70
71        // Get results only available for the current user.
72        $ands[] = '(owneruserid = ? OR owneruserid = ?)';
73        $params = array_merge($params, array(\core_search\manager::NO_OWNER_ID, $USER->id));
74
75        // Restrict it to the context where the user can access, we want this one cached.
76        // If the user can access all contexts $usercontexts value is just true, we don't need to filter
77        // in that case.
78        if ($usercontexts && is_array($usercontexts)) {
79            // Join all area contexts into a single array and implode.
80            $allcontexts = array();
81            foreach ($usercontexts as $areaid => $areacontexts) {
82                if (!empty($filters->areaids) && !in_array($areaid, $filters->areaids)) {
83                    // Skip unused areas.
84                    continue;
85                }
86                foreach ($areacontexts as $contextid) {
87                    // Ensure they are unique.
88                    $allcontexts[$contextid] = $contextid;
89                }
90            }
91            if (empty($allcontexts)) {
92                // This means there are no valid contexts for them, so they get no results.
93                return array();
94            }
95
96            list($contextsql, $contextparams) = $DB->get_in_or_equal($allcontexts);
97            $ands[] = 'contextid ' . $contextsql;
98            $params = array_merge($params, $contextparams);
99        }
100
101        // Course id filter.
102        if (!empty($filters->courseids)) {
103            list($conditionsql, $conditionparams) = $DB->get_in_or_equal($filters->courseids);
104            $ands[] = 'courseid ' . $conditionsql;
105            $params = array_merge($params, $conditionparams);
106        }
107
108        // Area id filter.
109        if (!empty($filters->areaids)) {
110            list($conditionsql, $conditionparams) = $DB->get_in_or_equal($filters->areaids);
111            $ands[] = 'areaid ' . $conditionsql;
112            $params = array_merge($params, $conditionparams);
113        }
114
115        if (!empty($filters->title)) {
116            $ands[] = $DB->sql_like('title', '?', false, false);
117            $params[] = $filters->title;
118        }
119
120        if (!empty($filters->timestart)) {
121            $ands[] = 'modified >= ?';
122            $params[] = $filters->timestart;
123        }
124        if (!empty($filters->timeend)) {
125            $ands[] = 'modified <= ?';
126            $params[] = $filters->timeend;
127        }
128
129        // And finally the main query after applying all AND filters.
130        if (!empty($filters->q)) {
131            switch ($DB->get_dbfamily()) {
132                case 'postgres':
133                    $ands[] = "(" .
134                        "to_tsvector('simple', title) @@ plainto_tsquery('simple', ?) OR ".
135                        "to_tsvector('simple', content) @@ plainto_tsquery('simple', ?) OR ".
136                        "to_tsvector('simple', description1) @@ plainto_tsquery('simple', ?) OR ".
137                        "to_tsvector('simple', description2) @@ plainto_tsquery('simple', ?)".
138                        ")";
139                    $params[] = $filters->q;
140                    $params[] = $filters->q;
141                    $params[] = $filters->q;
142                    $params[] = $filters->q;
143                    break;
144                case 'mysql':
145                    if ($DB->is_fulltext_search_supported()) {
146                        $ands[] = "MATCH (title, content, description1, description2) AGAINST (?)";
147                        $params[] = $filters->q;
148
149                        // Sorry for the hack, but it does not seem that we will have a solution for
150                        // this soon (https://bugs.mysql.com/bug.php?id=78485).
151                        if ($filters->q === '*') {
152                            return array();
153                        }
154                    } else {
155                        // Clumsy version for mysql versions with no fulltext support.
156                        list($queryand, $queryparams) = $this->get_simple_query($filters->q);
157                        $ands[] = $queryand;
158                        $params = array_merge($params, $queryparams);
159                    }
160                    break;
161                case 'mssql':
162                    if ($DB->is_fulltext_search_supported()) {
163                        $ands[] = "CONTAINS ((title, content, description1, description2), ?)";
164                        // Special treatment for double quotes:
165                        // - Puntuation is ignored so we can get rid of them.
166                        // - Phrases should be enclosed in double quotation marks.
167                        $params[] = '"' . str_replace('"', '', $filters->q) . '"';
168                    } else {
169                        // Clumsy version for mysql versions with no fulltext support.
170                        list($queryand, $queryparams) = $this->get_simple_query($filters->q);
171                        $ands[] = $queryand;
172                        $params = array_merge($params, $queryparams);
173                    }
174                    break;
175                default:
176                    list($queryand, $queryparams) = $this->get_simple_query($filters->q);
177                    $ands[] = $queryand;
178                    $params = array_merge($params, $queryparams);
179                    break;
180            }
181        }
182
183        // It is limited to $limit, no need to use recordsets.
184        $documents = $DB->get_records_select('search_simpledb_index', implode(' AND ', $ands), $params, '', '*', 0, $limit);
185
186        // Hopefully database cached results as this applies the same filters than above.
187        $this->totalresults = $DB->count_records_select('search_simpledb_index', implode(' AND ', $ands), $params);
188
189        $numgranted = 0;
190
191        // Iterate through the results checking its availability and whether they are available for the user or not.
192        $docs = array();
193        foreach ($documents as $docdata) {
194            if ($docdata->owneruserid != \core_search\manager::NO_OWNER_ID && $docdata->owneruserid != $USER->id) {
195                // If owneruserid is set, no other user should be able to access this record.
196                continue;
197            }
198
199            if (!$searcharea = $this->get_search_area($docdata->areaid)) {
200                $this->totalresults--;
201                continue;
202            }
203
204            // Switch id back to the document id.
205            $docdata->id = $docdata->docid;
206            unset($docdata->docid);
207
208            $access = $searcharea->check_access($docdata->itemid);
209            switch ($access) {
210                case \core_search\manager::ACCESS_DELETED:
211                    $this->delete_by_id($docdata->id);
212                    $this->totalresults--;
213                    break;
214                case \core_search\manager::ACCESS_DENIED:
215                    $this->totalresults--;
216                    break;
217                case \core_search\manager::ACCESS_GRANTED:
218                    $numgranted++;
219                    $docs[] = $this->to_document($searcharea, (array)$docdata);
220                    break;
221            }
222
223            // This should never happen.
224            if ($numgranted >= $limit) {
225                $docs = array_slice($docs, 0, $limit, true);
226                break;
227            }
228        }
229
230        return $docs;
231    }
232
233    /**
234     * Adds a document to the search engine.
235     *
236     * This does not commit to the search engine.
237     *
238     * @param \core_search\document $document
239     * @param bool $fileindexing True if file indexing is to be used
240     * @return bool False if the file was skipped or failed, true on success
241     */
242    public function add_document($document, $fileindexing = false) {
243        global $DB;
244
245        $doc = (object)$document->export_for_engine();
246
247        // Moodle's ids using DML are always autoincremented.
248        $doc->docid = $doc->id;
249        unset($doc->id);
250
251        $id = $DB->get_field('search_simpledb_index', 'id', array('docid' => $doc->docid));
252        try {
253            if ($id) {
254                $doc->id = $id;
255                $DB->update_record('search_simpledb_index', $doc);
256            } else {
257                $DB->insert_record('search_simpledb_index', $doc);
258            }
259
260        } catch (\dml_exception $ex) {
261            debugging('dml error while trying to insert document with id ' . $doc->docid . ': ' . $ex->getMessage(),
262                DEBUG_DEVELOPER);
263            return false;
264        }
265
266        return true;
267    }
268
269    /**
270     * Deletes the specified document.
271     *
272     * @param string $id The document id to delete
273     * @return void
274     */
275    public function delete_by_id($id) {
276        global $DB;
277        $DB->delete_records('search_simpledb_index', array('docid' => $id));
278    }
279
280    /**
281     * Delete all area's documents.
282     *
283     * @param string $areaid
284     * @return void
285     */
286    public function delete($areaid = null) {
287        global $DB;
288        if ($areaid) {
289            $DB->delete_records('search_simpledb_index', array('areaid' => $areaid));
290        } else {
291            $DB->delete_records('search_simpledb_index');
292        }
293    }
294
295    /**
296     * Checks that the required table was installed.
297     *
298     * @return true|string Returns true if all good or an error string.
299     */
300    public function is_server_ready() {
301        global $DB;
302        if (!$DB->get_manager()->table_exists('search_simpledb_index')) {
303            return 'search_simpledb_index table does not exist';
304        }
305
306        return true;
307    }
308
309    /**
310     * It is always installed.
311     *
312     * @return true
313     */
314    public function is_installed() {
315        return true;
316    }
317
318    /**
319     * Returns the total results.
320     *
321     * Including skipped results.
322     *
323     * @return int
324     */
325    public function get_query_total_count() {
326        if (!is_null($this->totalresults)) {
327            // This is a just in case as we count total results in execute_query.
328            return \core_search\manager::MAX_RESULTS;
329        }
330
331        return $this->totalresults;
332    }
333
334    /**
335     * Returns the default query for db engines.
336     *
337     * @param string $q The query string
338     * @return array SQL string and params list
339     */
340    protected function get_simple_query($q) {
341        global $DB;
342
343        $sql = '(' .
344            $DB->sql_like('title', '?', false, false) . ' OR ' .
345            $DB->sql_like('content', '?', false, false) . ' OR ' .
346            $DB->sql_like('description1', '?', false, false) . ' OR ' .
347            $DB->sql_like('description2', '?', false, false) .
348            ')';
349        $params = array(
350            '%' . $q . '%',
351            '%' . $q . '%',
352            '%' . $q . '%',
353            '%' . $q . '%'
354        );
355        return array($sql, $params);
356    }
357
358    /**
359     * Simpledb supports deleting the index for a context.
360     *
361     * @param int $oldcontextid Context that has been deleted
362     * @return bool True to indicate that any data was actually deleted
363     * @throws \core_search\engine_exception
364     */
365    public function delete_index_for_context(int $oldcontextid) {
366        global $DB;
367        try {
368            $DB->delete_records('search_simpledb_index', ['contextid' => $oldcontextid]);
369        } catch (\dml_exception $e) {
370            throw new \core_search\engine_exception('dbupdatefailed');
371        }
372        return true;
373    }
374
375    /**
376     * Simpledb supports deleting the index for a course.
377     *
378     * @param int $oldcourseid
379     * @return bool True to indicate that any data was actually deleted
380     * @throws \core_search\engine_exception
381     */
382    public function delete_index_for_course(int $oldcourseid) {
383        global $DB;
384        try {
385            $DB->delete_records('search_simpledb_index', ['courseid' => $oldcourseid]);
386        } catch (\dml_exception $e) {
387            throw new \core_search\engine_exception('dbupdatefailed');
388        }
389        return true;
390    }
391}
392