Mercurial > libervia-backend

comparison src/memory/sqlite.py @ 2013:b536dd121da1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
backend (memory), frontends: improved history filtering: a "filters" dictionnary is now use to filter, it can have, for now, filtering on: - "body": filter only on the body (equivalent to former "search" parameter, but not case sensitive) - "search": fitler on body + source resource - "types": allowed types - "not_types": forbidden types primitivus now do searching using "search", i.e. source resource is now taken into account (and search is now case insensitive)
author Goffi <goffi@goffi.org>
date Mon, 18 Jul 2016 00:52:02 +0200
parents a45235d8dc93
children c3cac21157d4
comparison
equal deleted inserted replaced
2012:53587e738aca 2013:b536dd121da1
390 ret = [] 390 ret = []
391 for m in messages_data: 391 for m in messages_data:
392 ret.append((m['uid'], m['timestamp'], m['from'], m['to'], m['message'], m['subject'], m['type'], m['extra'])) 392 ret.append((m['uid'], m['timestamp'], m['from'], m['to'], m['message'], m['subject'], m['type'], m['extra']))
393 return ret 393 return ret
394 394
395 def historyGet(self, from_jid, to_jid, limit=None, between=True, search=None, profile=None): 395 def historyGet(self, from_jid, to_jid, limit=None, between=True, filters=None, profile=None):
396 """Retrieve messages in history 396 """Retrieve messages in history
397 397
398 @param from_jid (JID): source JID (full, or bare for catchall) 398 @param from_jid (JID): source JID (full, or bare for catchall)
399 @param to_jid (JID): dest JID (full, or bare for catchall) 399 @param to_jid (JID): dest JID (full, or bare for catchall)
400 @param limit (int): maximum number of messages to get: 400 @param limit (int): maximum number of messages to get:
404 @param search (unicode): pattern to filter the history results 404 @param search (unicode): pattern to filter the history results
405 @param profile (unicode): %(doc_profile)s 405 @param profile (unicode): %(doc_profile)s
406 @return: list of tuple as in [messageNew] 406 @return: list of tuple as in [messageNew]
407 """ 407 """
408 assert profile 408 assert profile
409 if filters is None:
410 filters = {}
409 if limit == 0: 411 if limit == 0:
410 return defer.succeed([]) 412 return defer.succeed([])
411 413
412 query_parts = ["SELECT uid, update_uid, source, dest, source_res, dest_res, timestamp, received_timestamp,\ 414 query_parts = [u"SELECT uid, update_uid, source, dest, source_res, dest_res, timestamp, received_timestamp,\
413 type, extra, message, message.language, subject, subject.language, thread_id, thread.parent_id\ 415 type, extra, message, message.language, subject, subject.language, thread_id, thread.parent_id\
414 FROM history LEFT JOIN message ON history.uid = message.history_uid\ 416 FROM history LEFT JOIN message ON history.uid = message.history_uid\
415 LEFT JOIN subject ON history.uid=subject.history_uid\ 417 LEFT JOIN subject ON history.uid=subject.history_uid\
416 LEFT JOIN thread ON history.uid=thread.history_uid\ 418 LEFT JOIN thread ON history.uid=thread.history_uid\
417 WHERE profile_id=? AND"] # FIXME: not sure if it's the best request, messages and subjects can appear several times here 419 WHERE profile_id=? AND"] # FIXME: not sure if it's the best request, messages and subjects can appear several times here
419 421
420 def test_jid(type_, _jid): 422 def test_jid(type_, _jid):
421 values.append(_jid.userhost()) 423 values.append(_jid.userhost())
422 if _jid.resource: 424 if _jid.resource:
423 values.append(_jid.resource) 425 values.append(_jid.resource)
424 return '(%s=? AND %s_res=?)' % (type_, type_) 426 return u'(%s=? AND %s_res=?)' % (type_, type_)
425 return '%s=?' % (type_, ) 427 return u'%s=?' % (type_, )
426 428
427 if between: 429 if between:
428 query_parts.append("((%s AND %s) OR (%s AND %s))" % (test_jid('source', from_jid), 430 query_parts.append(u"((%s AND %s) OR (%s AND %s))" % (test_jid('source', from_jid),
429 test_jid('dest', to_jid), 431 test_jid('dest', to_jid),
430 test_jid('source', to_jid), 432 test_jid('source', to_jid),
431 test_jid('dest', from_jid))) 433 test_jid('dest', from_jid)))
432 else: 434 else:
433 query_parts.append("%s AND %s" % (test_jid('source', from_jid), 435 query_parts.append(u"%s AND %s" % (test_jid('source', from_jid),
434 test_jid('dest', to_jid))) 436 test_jid('dest', to_jid)))
435 if search: 437
436 # TODO: use REGEXP (function to be defined) instead of GLOB: https://www.sqlite.org/lang_expr.html 438 if filters:
437 query_parts.append("AND message GLOB ?") 439 if 'body' in filters:
438 values.append("*%s*" % search) 440 # TODO: use REGEXP (function to be defined) instead of GLOB: https://www.sqlite.org/lang_expr.html
439 441 query_parts.append(u"AND message LIKE ?")
440 query_parts.append("ORDER BY timestamp DESC") # we reverse the order in sqliteHistoryToList 442 values.append(u"%{}%".format(filters['body']))
443 if 'search' in filters:
444 query_parts.append(u"AND (message LIKE ? OR source_res LIKE ?)")
445 values.extend([u"%{}%".format(filters['search'])] * 2)
446 if 'types' in filters:
447 types = filters['types'].split()
448 query_parts.append(u"AND type IN ({})".format(u','.join("?"*len(types))))
449 values.extend(types)
450 if 'not_types' in filters:
451 types = filters['not_types'].split()
452 query_parts.append(u"AND type NOT IN ({})".format(u','.join("?"*len(types))))
453 values.extend(types)
454
455
456 query_parts.append(u"ORDER BY timestamp DESC") # we reverse the order in sqliteHistoryToList
441 # we use DESC here so LIMIT keep the last messages 457 # we use DESC here so LIMIT keep the last messages
442 if limit is not None: 458 if limit is not None:
443 query_parts.append("LIMIT ?") 459 query_parts.append(u"LIMIT ?")
444 values.append(limit) 460 values.append(limit)
445 461
446 d = self.dbpool.runQuery(" ".join(query_parts), values) 462 d = self.dbpool.runQuery(u" ".join(query_parts), values)
447 d.addCallback(self.sqliteHistoryToList) 463 d.addCallback(self.sqliteHistoryToList)
448 d.addCallback(self.listDict2listTuple) 464 d.addCallback(self.listDict2listTuple)
449 return d 465 return d
450 466
451 #Private values 467 #Private values