Newer
Older
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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
<?php
/*********************************************************************
module.search.php
Search Engine for osTicket
This module defines the pieces for a search engine for osTicket.
Searching can be performed by various search engine backends which can
make use of the features of various search providers.
A reference search engine backend is provided which uses MySQL MyISAM
tables. This default backend should not be used on Galera clusters.
Jared Hancock <jared@osticket.com>
Peter Rotich <peter@osticket.com>
Copyright (c) 2006-2013 osTicket
http://www.osticket.com
Released under the GNU General Public License WITHOUT ANY WARRANTY.
See LICENSE.TXT for details.
vim: expandtab sw=4 ts=4 sts=4:
**********************************************************************/
abstract class SearchBackend {
static $id = false;
static $registry = array();
const SORT_RELEVANCE = 1;
const SORT_RECENT = 2;
const SORT_OLDEST = 3;
abstract function update($model, $id, $content, $new=false, $attrs=array());
abstract function find($query, $criteria, $model=false, $sort=array());
function register($backend=false) {
$backend = $backend ?: get_called_class();
if ($backend::$id == false)
throw new Exception('SearchBackend must define an ID');
static::$registry[$backend::$id] = $backend;
}
function getInstance($id) {
if (!isset(self::$registry[$id]))
return null;
return new self::$registry[$id]();
}
}
// Register signals to intercept saving of various content throughout the
// system
class SearchInterface {
var $backend;
function __construct() {
$this->bootstrap();
}
function find($query, $criteria, $model=false, $sort=array()) {
$query = Format::searchable($query);
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
return $this->backend->find($query, $criteria, $model, $sort);
}
function update($model, $id, $content, $new=false, $attrs=array()) {
if (!$this->backend)
return;
$this->backend->update($model, $id, $content, $new, $attrs);
}
function createModel($model) {
return $this->updateModel($model, true);
}
function updateModel($model, $new=false) {
// The MySQL backend does not need to index attributes of the
// various models, because those other attributes are available in
// the local database in other tables.
switch (true) {
case $model instanceof ThreadEntry:
// Only index an entry for threads if a human created the
// content
if (!$model->getUserId() && !$model->getStaffId())
break;
$this->update($model, $model->getId(),
$model->getBody()->getSearchable(), $new,
array(
'title' => $model->getTitle(),
'ticket_id' => $model->getTicketId(),
'created' => $model->getCreateDate(),
)
);
break;
case $model instanceof Ticket:
$cdata = array();
foreach ($model->loadDynamicData() as $a)
if ($v = $a->getSearchable())
$cdata[] = $v;
$this->update($model, $model->getId(),
trim(implode("\n", $cdata)),
$new,
array(
'title'=> Format::searchable($model->getSubject()),
'number'=> $model->getNumber(),
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
'status'=> $model->getStatus(),
'topic_id'=> $model->getTopicId(),
'priority_id'=> $model->getPriorityId(),
// Stats (comments, attachments)
// Access constraints
'dept_id'=> $model->getDeptId(),
'staff_id'=> $model->getStaffId(),
'team_id'=> $model->getTeamId(),
// Sorting and ranging preferences
'created'=> $model->getCreateDate(),
// Add last-updated timestamp
)
);
break;
case $model instanceof User:
$cdata = array();
foreach ($model->getDynamicData() as $e)
foreach ($e->getAnswers() as $tag=>$a)
if ($tag != 'subject' && ($v = $a->getSearchable()))
$cdata[] = $v;
$this->update($model, $model->getId(),
trim(implode("\n", $cdata)),
$new,
array(
'title'=> Format::searchable($model->getFullName()),
'emails'=> $model->emails->asArray(),
'org_id'=> $model->getOrgId(),
'created'=> $model->getCreateDate(),
)
);
break;
case $model instanceof Organization:
$cdata = array();
foreach ($model->getDynamicData() as $e)
foreach ($e->getAnswers() as $a)
if ($v = $a->getSearchable())
$cdata[] = $v;
$this->update($model, $model->getId(),
trim(implode("\n", $cdata)),
$new,
array(
'title'=> Format::searchable($model->getName()),
'created'=> $model->getCreateDate(),
)
);
break;
case $model instanceof FAQ:
$this->update($model, $model->getId(),
$model->getSearchableAnswer(),
$new,
array(
'title'=> Format::searchable($model->getQuestion()),
'keywords'=> $model->getKeywords(),
'topics'=> $model->getHelpTopicsIds(),
'category_id'=> $model->getCategoryId(),
'created'=> $model->getCreateDate(),
)
);
break;
default:
// Not indexed
break;
}
}
function bootstrap() {
// Determine the backend
if (defined('SEARCH_BACKEND'))
$bk = SearchBackend::getInstance(SEARCH_BACKEND);
if (!$bk && !($bk = SearchBackend::getInstance('mysql')))
// No backend registered or defined
return false;
$this->backend = $bk;
$this->backend->bootstrap();
$self = $this;
// Thread entries
// Tickets, which can be edited as well
// Knowledgebase articles (FAQ and canned responses)
// Users, organizations
Signal::connect('model.created', array($this, 'createModel'));
Signal::connect('model.updated', array($this, 'updateModel'));
#Signal::connect('model.deleted', array($this, 'deleteModel'));
}
}
class MysqlSearchBackend extends SearchBackend {
static $id = 'mysql';
static $BATCH_SIZE = 30;
// Only index 20 batches per cron run
var $max_batches = 60;
function __construct() {
$this->SEARCH_TABLE = TABLE_PREFIX . '_search';
}
function bootstrap() {
Signal::connect('cron', array($this, 'IndexOldStuff'));
}
function update($model, $id, $content, $new=false, $attrs=array()) {
switch (true) {
case $model instanceof ThreadEntry:
$type = 'H';
break;
case $model instanceof Ticket:
$attrs['title'] = $attrs['number'].' '.$attrs['title'];
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
$type = 'T';
break;
case $model instanceof User:
$content .= implode("\n", $attrs['emails']);
$type = 'U';
break;
case $model instanceof Organization:
$type = 'O';
break;
case $model instanceof FAQ:
$type = 'K';
break;
case $model instanceof AttachmentFile:
$type = 'F';
break;
default:
// Not indexed
return;
}
$title = $attrs['title'] ?: '';
if (!$content && !$title)
return;
$sql = 'REPLACE INTO '.$this->SEARCH_TABLE
. ' SET object_type='.db_input($type)
. ', object_id='.db_input($id)
. ', content='.db_input($content)
. ', title='.db_input($title);
return db_query($sql);
}
function find($query, $criteria=array(), $model=false, $sort=array()) {
global $thisstaff;
$mode = ' IN BOOLEAN MODE';
#if (count(explode(' ', $query)) == 1)
# $mode = ' WITH QUERY EXPANSION';
$search = 'MATCH (search.title, search.content) AGAINST ('
$tables = array();
$P = TABLE_PREFIX;
$sort = '';
if ($query) {
$tables[] = "(
SELECT object_type, object_id, $search AS `relevance`
FROM `{$P}_search` `search`
WHERE $search
) `search`";
$sort = 'ORDER BY `search`.`relevance`';
}
switch ($model) {
case false:
case 'Ticket':
$tables[] = "(select ticket_id as ticket_id from {$P}ticket
) B1 ON (B1.ticket_id = search.object_id and search.object_type = 'T')";
$tables[] = "(select A2.id as thread_id, A1.ticket_id from {$P}ticket A1
join {$P}ticket_thread A2 on (A1.ticket_id = A2.ticket_id)
) B2 ON (B2.thread_id = search.object_id and search.object_type = 'H')";
$tables[] = "(select A3.id as user_id, A1.ticket_id from {$P}user A3
join {$P}ticket A1 on (A1.user_id = A3.id)
) B3 ON (B3.user_id = search.object_id and search.object_type = 'U')";
$tables[] = "(select A4.id as org_id, A1.ticket_id from {$P}organization A4
join {$P}user A3 on (A3.org_id = A4.id) join {$P}ticket A1 on (A1.user_id = A3.id)
) B4 ON (B4.org_id = search.object_id and search.object_type = 'O')";
$key = 'COALESCE(B1.ticket_id, B2.ticket_id, B3.ticket_id, B4.ticket_id)';
$tables[] = "{$P}ticket A1 ON (A1.ticket_id = {$key})";
$cdata_search = false;
if ($criteria) {
foreach ($criteria as $name=>$value) {
switch ($name) {
case 'status':
case 'topic_id':
case 'staff_id':
case 'dept_id':
case 'user_id':
case 'isanswered':
case 'isoverdue':
$where[] = sprintf('A1.%s = %s', $name, db_input($value));
break;
case 'created__gte':
$where[] = sprintf('A1.created >= %s', db_input($value));
break;
case 'created__lte':
$where[] = sprintf('A1.created <= %s', db_input($value));
break;
case 'email':
case 'org_id':
case 'form_id':
default:
if (strpos($name, 'cdata.') === 0) {
// Search ticket CDATA table
$cdata_search = true;
$name = substr($name, 6);
$where[] = sprintf("cdata.%s = %s", $name, db_input($value));
}
if ($cdata_search)
$tables[] = TABLE_PREFIX.'ticket__cdata cdata'
.' ON (cdata.ticket_id = A1.ticket_id)';
// Always consider the current staff's access
$thisstaff->getDepts();
$access = array();
$access[] = '(A1.staff_id=' . db_input($thisstaff->getId())
.' AND A1.status="open")';
if (!$thisstaff->showAssignedOnly() && ($depts=$thisstaff->getDepts()))
$access[] = 'A1.dept_id IN ('
. ($depts ? implode(',', db_input($depts)) : 0)
. ')';
if (($teams = $thisstaff->getTeams()) && count(array_filter($teams)))
$access[] = 'A1.team_id IN ('
.implode(',', db_input(array_filter($teams)))
.') AND A1.status="open"';
$where[] = '(' . implode(' OR ', $access) . ')';
// TODO: Consider sorting preferences
. implode(' LEFT JOIN ', $tables)
. ' WHERE ' . implode(' AND ', $where)
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
}
$res = db_query($sql);
$object_ids = array();
while ($row = db_fetch_row($res))
$object_ids[] = $row[0];
return $object_ids;
}
static function createSearchTable() {
$sql = 'CREATE TABLE '.TABLE_PREFIX.'_search (
`object_type` varchar(8) not null,
`object_id` int(11) unsigned not null,
`title` text collate utf8_general_ci,
`content` text collate utf8_general_ci,
primary key `object` (`object_type`, `object_id`),
fulltext key `search` (`title`, `content`)
) ENGINE=MyISAM CHARSET=utf8';
return db_query($sql);
}
/**
* Cooperates with the cron system to automatically find content that is
* not index in the _search table and add it to the index.
*/
function IndexOldStuff() {
$class = get_class();
$auto_create = function($db_error) use ($class) {
if ($db_error != 1146)
// Perform the standard error handling
return true;
// Create the search table automatically
$class::createSearchTable();
};
// THREADS ----------------------------------
$sql = "SELECT A1.`id`, A1.`title`, A1.`body`, A1.`format` FROM `".TICKET_THREAD_TABLE."` A1
LEFT JOIN `".TABLE_PREFIX."_search` A2 ON (A1.`id` = A2.`object_id` AND A2.`object_type`='H')
WHERE A2.`object_id` IS NULL AND (A1.poster <> 'SYSTEM')
AND (LENGTH(A1.`title`) + LENGTH(A1.`body`) > 0)
ORDER BY A1.`id` DESC";
if (!($res = db_query_unbuffered($sql, $auto_create)))
return false;
while ($row = db_fetch_row($res)) {
$body = ThreadBody::fromFormattedText($row[2], $row[3]);
$body = $body->getSearchable();
$title = Format::searchable($row[1]);
if (!$body && !$title)
continue;
$record = array('H', $row[0], $title, $body);
if (!$this->__index($record))
return;
}
// TICKETS ----------------------------------
$sql = "SELECT A1.`ticket_id` FROM `".TICKET_TABLE."` A1
LEFT JOIN `".TABLE_PREFIX."_search` A2 ON (A1.`ticket_id` = A2.`object_id` AND A2.`object_type`='T')
WHERE A2.`object_id` IS NULL
ORDER BY A1.`ticket_id` DESC";
if (!($res = db_query_unbuffered($sql, $auto_create)))
return false;
while ($row = db_fetch_row($res)) {
$ticket = Ticket::lookup($row[0]);
$cdata = $ticket->loadDynamicData();
$content = array();
foreach ($cdata as $k=>$a)
if ($k != 'subject' && ($v = $a->getSearchable()))
$content[] = $v;
$record = array('T', $ticket->getId(),
Format::searchable($ticket->getNumber().' '.$ticket->getSubject()),
if (!$this->__index($record))
return;
}
// USERS ------------------------------------
$sql = "SELECT A1.`id` FROM `".USER_TABLE."` A1
LEFT JOIN `".TABLE_PREFIX."_search` A2 ON (A1.`id` = A2.`object_id` AND A2.`object_type`='U')
WHERE A2.`object_id` IS NULL
ORDER BY A1.`id` DESC";
if (!($res = db_query_unbuffered($sql, $auto_create)))
return false;
while ($row = db_fetch_row($res)) {
$user = User::lookup($row[0]);
$cdata = $user->getDynamicData();
$content = array();
foreach ($user->emails as $e)
$content[] = $e->address;
foreach ($cdata as $e)
foreach ($e->getAnswers() as $a)
if ($c = $a->getSearchable())
$content[] = $c;
$record = array('U', $user->getId(),
Format::searchable($user->getFullName()),
trim(implode("\n", $content)));
if (!$this->__index($record))
return;
}
// ORGANIZATIONS ----------------------------
$sql = "SELECT A1.`id` FROM `".ORGANIZATION_TABLE."` A1
LEFT JOIN `".TABLE_PREFIX."_search` A2 ON (A1.`id` = A2.`object_id` AND A2.`object_type`='O')
WHERE A2.`object_id` IS NULL
ORDER BY A1.`id` DESC";
if (!($res = db_query_unbuffered($sql, $auto_create)))
return false;
while ($row = db_fetch_row($res)) {
$org = Organization::lookup($row[0]);
$cdata = $org->getDynamicData();
$content = array();
foreach ($cdata as $e)
foreach ($e->getAnswers() as $a)
if ($c = $a->getSearchable())
$content[] = $c;
$record = array('O', $org->getId(),
Format::searchable($org->getName()),
trim(implode("\n", $content)));
if (!$this->__index($record))
return null;
}
// KNOWLEDGEBASE ----------------------------
require_once INCLUDE_DIR . 'class.faq.php';
$sql = "SELECT A1.`faq_id` FROM `".FAQ_TABLE."` A1
LEFT JOIN `".TABLE_PREFIX."_search` A2 ON (A1.`faq_id` = A2.`object_id` AND A2.`object_type`='K')
WHERE A2.`object_id` IS NULL
ORDER BY A1.`faq_id` DESC";
if (!($res = db_query_unbuffered($sql, $auto_create)))
return false;
while ($row = db_fetch_row($res)) {
$faq = FAQ::lookup($row[0]);
$q = $faq->getQuestion();
if ($k = $faq->getKeywords())
$q = $k.' '.$q;
$record = array('K', $faq->getId(),
Format::searchable($q),
if (!$this->__index($record))
return;
}
// FILES ------------------------------------
// Flush non-full batch of records
$this->__index(null, true);
function __index($record, $force_flush=false) {
static $queue = array();
if ($record)
$queue[] = $record;
elseif (!$queue)
return;
if (!$force_flush && count($queue) < $this::$BATCH_SIZE)
return true;
foreach ($queue as &$r)
$r = sprintf('(%s)', implode(',', db_input($r)));
unset($r);
$sql = 'INSERT INTO `'.TABLE_PREFIX.'_search` (`object_type`, `object_id`, `title`, `content`)
VALUES '.implode(',', $queue);
if (!db_query($sql) || count($queue) != db_affected_rows())
throw new Exception('Unable to index content');
if (!--$this->max_batches)
return null;
}
}
MysqlSearchBackend::register();