Mercurial > libervia-backend
diff src/memory/sqlite.py @ 2013:b536dd121da1
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 |
line wrap: on
line diff
--- a/src/memory/sqlite.py Sun Jul 17 19:44:15 2016 +0200 +++ b/src/memory/sqlite.py Mon Jul 18 00:52:02 2016 +0200 @@ -392,7 +392,7 @@ ret.append((m['uid'], m['timestamp'], m['from'], m['to'], m['message'], m['subject'], m['type'], m['extra'])) return ret - def historyGet(self, from_jid, to_jid, limit=None, between=True, search=None, profile=None): + def historyGet(self, from_jid, to_jid, limit=None, between=True, filters=None, profile=None): """Retrieve messages in history @param from_jid (JID): source JID (full, or bare for catchall) @@ -406,10 +406,12 @@ @return: list of tuple as in [messageNew] """ assert profile + if filters is None: + filters = {} if limit == 0: return defer.succeed([]) - query_parts = ["SELECT uid, update_uid, source, dest, source_res, dest_res, timestamp, received_timestamp,\ + query_parts = [u"SELECT uid, update_uid, source, dest, source_res, dest_res, timestamp, received_timestamp,\ type, extra, message, message.language, subject, subject.language, thread_id, thread.parent_id\ FROM history LEFT JOIN message ON history.uid = message.history_uid\ LEFT JOIN subject ON history.uid=subject.history_uid\ @@ -421,29 +423,43 @@ values.append(_jid.userhost()) if _jid.resource: values.append(_jid.resource) - return '(%s=? AND %s_res=?)' % (type_, type_) - return '%s=?' % (type_, ) + return u'(%s=? AND %s_res=?)' % (type_, type_) + return u'%s=?' % (type_, ) if between: - query_parts.append("((%s AND %s) OR (%s AND %s))" % (test_jid('source', from_jid), + query_parts.append(u"((%s AND %s) OR (%s AND %s))" % (test_jid('source', from_jid), test_jid('dest', to_jid), test_jid('source', to_jid), test_jid('dest', from_jid))) else: - query_parts.append("%s AND %s" % (test_jid('source', from_jid), + query_parts.append(u"%s AND %s" % (test_jid('source', from_jid), test_jid('dest', to_jid))) - if search: - # TODO: use REGEXP (function to be defined) instead of GLOB: https://www.sqlite.org/lang_expr.html - query_parts.append("AND message GLOB ?") - values.append("*%s*" % search) - query_parts.append("ORDER BY timestamp DESC") # we reverse the order in sqliteHistoryToList + if filters: + if 'body' in filters: + # TODO: use REGEXP (function to be defined) instead of GLOB: https://www.sqlite.org/lang_expr.html + query_parts.append(u"AND message LIKE ?") + values.append(u"%{}%".format(filters['body'])) + if 'search' in filters: + query_parts.append(u"AND (message LIKE ? OR source_res LIKE ?)") + values.extend([u"%{}%".format(filters['search'])] * 2) + if 'types' in filters: + types = filters['types'].split() + query_parts.append(u"AND type IN ({})".format(u','.join("?"*len(types)))) + values.extend(types) + if 'not_types' in filters: + types = filters['not_types'].split() + query_parts.append(u"AND type NOT IN ({})".format(u','.join("?"*len(types)))) + values.extend(types) + + + query_parts.append(u"ORDER BY timestamp DESC") # we reverse the order in sqliteHistoryToList # we use DESC here so LIMIT keep the last messages if limit is not None: - query_parts.append("LIMIT ?") + query_parts.append(u"LIMIT ?") values.append(limit) - d = self.dbpool.runQuery(" ".join(query_parts), values) + d = self.dbpool.runQuery(u" ".join(query_parts), values) d.addCallback(self.sqliteHistoryToList) d.addCallback(self.listDict2listTuple) return d