lib.php 18.9 KB
Newer Older
Martyn Smith's avatar
Martyn Smith committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
/**
 * This program is part of Mahara
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
 *
 * @package    mahara
 * @subpackage search-internal
 * @author     Martyn Smith <martyn@catalyst.net.nz>
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL
 * @copyright  (C) 2006,2007 Catalyst IT Ltd http://catalyst.net.nz
 *
 */

defined('INTERNAL') || die();

/**
30
 * The internal search plugin which searches against the
Martyn Smith's avatar
Martyn Smith committed
31
32
33
34
35
36
37
 * Mahara database.
 */
class PluginSearchInternal extends PluginSearch {

    /**
     * Implement user searching with SQL
     *
38
39
     * NOTE: user with ID zero should never be returned
     *
Martyn Smith's avatar
Martyn Smith committed
40
41
42
43
44
45
46
47
     * @param string  The query string
     * @param integer How many results to return
     * @param integer What result to start at (0 == first result)
     * @return array  A data structure containing results looking like ...
     *         $results = array(
     *               count   => integer, // total number of results
     *               limit   => integer, // how many results are returned
     *               offset  => integer, // starting from which result
48
     *               data    => array(   // the result records
Martyn Smith's avatar
Martyn Smith committed
49
     *                   array(
50
51
52
53
54
55
56
     *                       id            => integer,
     *                       username      => string,
     *                       institution   => string,
     *                       firstname     => string,
     *                       lastname      => string,
     *                       preferredname => string,
     *                       email         => string,
Martyn Smith's avatar
Martyn Smith committed
57
58
     *                   ),
     *                   array(
59
60
61
62
63
64
65
     *                       id            => integer,
     *                       username      => string,
     *                       institution   => string,
     *                       firstname     => string,
     *                       lastname      => string,
     *                       preferredname => string,
     *                       email         => string,
Martyn Smith's avatar
Martyn Smith committed
66
67
68
69
70
71
     *                   ),
     *                   array(...),
     *               ),
     *           );
     */
    public static function search_user($query_string, $limit, $offset = 0) {
72
73
        safe_require('artefact', 'internal');
        $publicfields = array_keys(ArtefactTypeProfile::get_public_fields());
74
75
76
        if (empty($publicfields)) {
            $publicfields = array('preferredname');
        }
77
        $prefix = get_config('dbprefix');
78
79
80
81
82
83
84
        if (is_postgres()) {
            return self::search_user_pg($query_string, $limit, $offset, $prefix, $publicfields);
        } 
        else if (is_mysql()) {
            return self::search_user_my($query_string, $limit, $offset, $prefix, $publicfields);
        }
        else {
85
86
87
88
89
90
            throw new SQLException('search_user() is not implemented for your database engine (' . get_config('dbtype') . ')');
        }
    }

    public static function search_user_pg($query_string, $limit, $offset, $prefix, $publicfields) {
        $fieldlist = "('" . join("','", $publicfields) . "')";
Martyn Smith's avatar
Martyn Smith committed
91

92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
        $count = get_field_sql('
            SELECT 
                COUNT(DISTINCT u.id)
            FROM
                ' . $prefix . 'usr u
                LEFT JOIN ' . $prefix . 'artefact a ON u.id=a.owner
            WHERE
                u.id <> 0 AND u.active = 1
                AND ((
                        u.preferredname IS NULL
                        AND (
                            u.firstname ILIKE \'%\' || ? || \'%\'
                            OR u.lastname ILIKE \'%\' || ? || \'%\'
                        )
                    )
                    OR (
                        a.artefacttype IN ' . $fieldlist . '
                        AND ( a.title ILIKE \'%\' || ? || \'%\')
                    )
                )
            ',
            array($query_string, $query_string, $query_string)
        );

        if ($count > 0) {
            $data = get_records_sql_array('
                SELECT DISTINCT ON (u.firstname, u.lastname, u.id)
                    u.id, u.username, u.institution, u.firstname, u.lastname, u.preferredname, u.email, u.staff
                FROM ' . $prefix . 'artefact a
                    INNER JOIN ' . $prefix .'usr u ON u.id = a.owner
122
                WHERE
Martyn Smith's avatar
Martyn Smith committed
123
                    u.id <> 0 AND u.active = 1
Martyn Smith's avatar
Martyn Smith committed
124
125
126
127
128
129
130
131
132
133
134
135
                    AND ((
                            u.preferredname IS NULL
                            AND (
                                u.firstname ILIKE \'%\' || ? || \'%\'
                                OR u.lastname ILIKE \'%\' || ? || \'%\'
                            )
                        )
                        OR (
                            a.artefacttype IN ' . $fieldlist . '
                            AND ( a.title ILIKE \'%\' || ? || \'%\')
                        )
                    )
136
137
138
139
                ORDER BY u.firstname, u.lastname, u.id',
            array($query_string, $query_string, $query_string),
            $offset,
            $limit);
140

141
142
143
            if ($data) {
                foreach ($data as &$item) {
                    $item = (array)$item;
144
                }
145
            }
Martyn Smith's avatar
Martyn Smith committed
146
147
        }
        else {
148
            $data = false;
Nigel McNie's avatar
Nigel McNie committed
149
150
151
152
153
154
155
156
157
        }

        return array(
            'count'   => $count,
            'limit'   => $limit,
            'offset'  => $offset,
            'data'    => $data,
        );
    }
158
159
160
161
162
163

    public static function search_user_my($query_string, $limit, $offset, $prefix, $publicfields) {
        $fieldlist = "('" . join("','", $publicfields) . "')";

        $count = get_field_sql('
            SELECT 
164
                COUNT(DISTINCT owner)
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
            FROM
                ' . $prefix . 'usr u
                LEFT JOIN ' . $prefix . 'artefact a ON u.id=a.owner
            WHERE
                u.id <> 0 AND u.active = 1
                AND ((
                        u.preferredname IS NULL
                        AND (
                            u.firstname LIKE \'%\' || ? || \'%\'
                            OR u.lastname LIKE \'%\' || ? || \'%\'
                        )
                    )
                    OR (
                        a.artefacttype IN ' . $fieldlist . '
                        AND ( a.title LIKE \'%\' || ? || \'%\')
                    )
                )
            ',
            array($query_string, $query_string, $query_string)
        );

        if ($count > 0) {
187
188
189
190
191
            // @todo This is quite possibly not correct. See the postgres 
            // query. It should be DISTINCT ON the fields as specified by the 
            // postgres query. There is a workaround by using SELECT * followed 
            // by GROUP BY 1, 2, 3, but it's not really valid SQL. The other 
            // way is to rewrite the query using a self join on the usr table.
192
            $data = get_records_sql_array('
193
                SELECT DISTINCT
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
                    u.id, u.username, u.institution, u.firstname, u.lastname, u.preferredname, u.email, u.staff
                FROM ' . $prefix . 'artefact a
                    INNER JOIN ' . $prefix .'usr u ON u.id = a.owner
                WHERE
                    u.id <> 0 AND u.active = 1
                    AND ((
                            u.preferredname IS NULL
                            AND (
                                u.firstname LIKE \'%\' || ? || \'%\'
                                OR u.lastname LIKE \'%\' || ? || \'%\'
                            )
                        )
                        OR (
                            a.artefacttype IN ' . $fieldlist . '
                            AND ( a.title LIKE \'%\' || ? || \'%\')
                        )
                    )
                ORDER BY u.firstname, u.lastname, u.id',
            array($query_string, $query_string, $query_string),
            $offset,
            $limit);

            if ($data) {
                foreach ($data as &$item) {
                    $item = (array)$item;
                }
            }
        }
        else {
            $data = false;
        }

        return array(
            'count'   => $count,
            'limit'   => $limit,
            'offset'  => $offset,
            'data'    => $data,
        );
    }
Nigel McNie's avatar
Nigel McNie committed
233
234
235
236
237
238
239
240
241
242
243
244
245
246
    
    /**
     * Implement group searching with SQL
     *
     * @param string  The query string
     * @param integer How many results to return
     * @param integer What result to start at (0 == first result)
     * @return array  A data structure containing results looking like ...
     *         $results = array(
     *               count   => integer, // total number of results
     *               limit   => integer, // how many results are returned
     *               offset  => integer, // starting from which result
     *               data    => array(   // the result records
     *                   array(
247
248
249
250
251
252
     *                       id          => integer,
     *                       name        => string,
     *                       owner       => integer,
     *                       description => string,
     *                       ctime       => string,
     *                       mtime       => string,
Nigel McNie's avatar
Nigel McNie committed
253
254
     *                   ),
     *                   array(
255
256
257
258
259
260
     *                       id          => integer,
     *                       name        => string,
     *                       owner       => integer,
     *                       description => string,
     *                       ctime       => string,
     *                       mtime       => string,
Nigel McNie's avatar
Nigel McNie committed
261
262
263
264
265
266
     *                   ),
     *                   array(...),
     *               ),
     *           );
     */
    public static function search_group($query_string, $limit, $offset = 0) {
267
268
269
270
271
272
273
        if (is_postgres()) {
            return self::search_group_pg($query_string, $limit, $offset);
        } 
        else if (is_mysql()) {
            return self::search_group_my($query_string, $limit, $offset);
        }
        else {
Nigel McNie's avatar
Nigel McNie committed
274
275
            throw new SQLException('search_group() is not implemented for your database engine (' . get_config('dbtype') . ')');
        }
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
    }

    public static function search_group_pg($query_string, $limit, $offset) {
        global $USER;
        $data = get_records_sql_array("
            SELECT
                id, name, owner, description, ctime, mtime
            FROM
                " . get_config('dbprefix') . "usr_group u
            WHERE
                owner = ?
                AND (
                    name ILIKE '%' || ? || '%' 
                    OR description ILIKE '%' || ? || '%' 
                )
            ",
            array($USER->get('id'), $query_string, $query_string),
            $offset,
            $limit
        );

        $count = get_field_sql("
            SELECT
                COUNT(*)
            FROM
                " . get_config('dbprefix') . "usr_group u
            WHERE
                owner = ?
                AND (
                    name ILIKE '%' || ? || '%' 
                    OR description ILIKE '%' || ? || '%' 
                )
        ",
            array($USER->get('id'), $query_string, $query_string)
        );
Nigel McNie's avatar
Nigel McNie committed
311
312
313
314
315
316
317
318

        return array(
            'count'   => $count,
            'limit'   => $limit,
            'offset'  => $offset,
            'data'    => $data,
        );
    }
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360

    public static function search_group_my($query_string, $limit, $offset) {
        global $USER;
        $data = get_records_sql_array("
            SELECT
                id, name, owner, description, ctime, mtime
            FROM
                " . get_config('dbprefix') . "usr_group u
            WHERE
                owner = ?
                AND (
                    name LIKE '%' || ? || '%' 
                    OR description LIKE '%' || ? || '%' 
                )
            ",
            array($USER->get('id'), $query_string, $query_string),
            $offset,
            $limit
        );

        $count = get_field_sql("
            SELECT
                COUNT(*)
            FROM
                " . get_config('dbprefix') . "usr_group u
            WHERE
                owner = ?
                AND (
                    name LIKE '%' || ? || '%' 
                    OR description LIKE '%' || ? || '%' 
                )
        ",
            array($USER->get('id'), $query_string, $query_string)
        );

        return array(
            'count'   => $count,
            'limit'   => $limit,
            'offset'  => $offset,
            'data'    => $data,
        );
    }
Nigel McNie's avatar
Nigel McNie committed
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
    
    /**
     * Implement community searching with SQL
     *
     * @param string  The query string
     * @param integer How many results to return
     * @param integer What result to start at (0 == first result)
     * @return array  A data structure containing results looking like ...
     *         $results = array(
     *               count   => integer, // total number of results
     *               limit   => integer, // how many results are returned
     *               offset  => integer, // starting from which result
     *               data    => array(   // the result records
     *                   array(
     *                       id            => integer,
376
377
378
379
380
381
     *                       name          => string,
     *                       description   => string,
     *                       jointype      => string,
     *                       owner         => string,
     *                       ctime         => string,
     *                       mtime         => string,
Nigel McNie's avatar
Nigel McNie committed
382
383
384
     *                   ),
     *                   array(
     *                       id            => integer,
385
386
387
388
389
390
     *                       name          => string,
     *                       description   => string,
     *                       jointype      => string,
     *                       owner         => string,
     *                       ctime         => string,
     *                       mtime         => string,
Nigel McNie's avatar
Nigel McNie committed
391
392
393
394
395
     *                   ),
     *                   array(...),
     *               ),
     *           );
     */
396
    public static function search_community($query_string, $limit, $offset=0, $all=false) {
397
398
399
400
401
402
403
        if (is_postgres()) {
            return self::search_community_pg($query_string, $limit, $offset, $all);
        } 
        else if (is_mysql()) {
            return self::search_community_my($query_string, $limit, $offset, $all);
        } 
        else {
Nigel McNie's avatar
Nigel McNie committed
404
            throw new SQLException('search_community() is not implemented for your database engine (' . get_config('dbtype') . ')');
Martyn Smith's avatar
Martyn Smith committed
405
        }
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
    }

    public static function search_community_pg($query_string, $limit, $offset, $all) {
        global $USER;
        $sql = "
            SELECT
                id, name, description, jointype, owner, ctime, mtime
            FROM
                " . get_config('dbprefix') . "community
            WHERE (
                name ILIKE '%' || ? || '%' 
                OR description ILIKE '%' || ? || '%' 
            )";
        $values = array($query_string, $query_string);
        if (!$all) {
            $sql .=  "AND ( 
                owner = ? OR id IN (
                    SELECT community FROM " . get_config('dbprefix') . "community_member WHERE member = ?
                )
            )";
            $values[] = $USER->get('id');
            $values[] = $USER->get('id');
        }
        $data = get_records_sql_array($sql, $values, $offset, $limit);

        $sql = "
            SELECT
                COUNT(*)
            FROM
                " . get_config('dbprefix') . "community u
            WHERE (
                name ILIKE '%' || ? || '%' 
                OR description ILIKE '%' || ? || '%' 
            )";
        if (!$all) {
            $sql .= "AND ( 
                    owner = ? OR id IN (
                        SELECT community FROM " . get_config('dbprefix') . "community_member WHERE member = ?
                    )
                )
            ";
        }
        $count = get_field_sql($sql, $values);
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498

        return array(
            'count'   => $count,
            'limit'   => $limit,
            'offset'  => $offset,
            'data'    => $data,
        );
    }

    public static function search_community_my($query_string, $limit, $offset, $all) {
        global $USER;
        $sql = "
            SELECT
                id, name, description, jointype, owner, ctime, mtime
            FROM
                " . get_config('dbprefix') . "community
            WHERE (
                name LIKE '%' || ? || '%' 
                OR description LIKE '%' || ? || '%' 
            )";
        $values = array($query_string, $query_string);
        if (!$all) {
            $sql .=  "AND ( 
                owner = ? OR id IN (
                    SELECT community FROM " . get_config('dbprefix') . "community_member WHERE member = ?
                )
            )";
            $values[] = $USER->get('id');
            $values[] = $USER->get('id');
        }
        $data = get_records_sql_array($sql, $values, $offset, $limit);

        $sql = "
            SELECT
                COUNT(*)
            FROM
                " . get_config('dbprefix') . "community u
            WHERE (
                name LIKE '%' || ? || '%' 
                OR description LIKE '%' || ? || '%' 
            )";
        if (!$all) {
            $sql .= "AND ( 
                    owner = ? OR id IN (
                        SELECT community FROM " . get_config('dbprefix') . "community_member WHERE member = ?
                    )
                )
            ";
        }
        $count = get_field_sql($sql, $values);
Martyn Smith's avatar
Martyn Smith committed
499
500
501
502
503

        return array(
            'count'   => $count,
            'limit'   => $limit,
            'offset'  => $offset,
504
            'data'    => $data,
Martyn Smith's avatar
Martyn Smith committed
505
506
        );
    }
Martyn Smith's avatar
Martyn Smith committed
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526

    /**
     * Given a query string and limits, return an array of matching objects
     * owned by the current user.  Possible return types are ...
     *   - artefact
     *   - view
     *
     * Implementations of this search should search across tags for artefacts
     * and views at a minimum. Ideally the search would also index
     * title/description and other metadata for these objects.
     *
     * @param string  The query string
     * @param integer How many results to return
     * @param integer What result to start at (0 == first result)
     * @param string  Type to search for (either 'all' or one of the types above).
     * 
     */
    public static function self_search($query_string, $limit, $offset, $type = 'all') {
        throw new Exception('TODO: implement me!');
    }
Martyn Smith's avatar
Martyn Smith committed
527
528
529
}

?>