def match_time(self, d1, d2, leeway_seconds=0): """Whether datetimes might be same but from different timezones.""" result = False d1, d2 = (d1, d2) if d1 and d2 and (d1 < d2) else (d2, d1) delta = d2 - d1 if d1 and d2 else datetime.timedelta() if util.timedelta_seconds(delta) > 24 * 3600: delta = datetime.timedelta() # Skip if not even within same day for hour in range(int(util.timedelta_seconds(delta) / 3600) + 1): d1plus = d1 + datetime.timedelta(hours=hour) result = util.timedelta_seconds(d2 - d1plus) < leeway_seconds if result: break # break for hour in range(.. return result
def makeKeywordsSQL(self, keywords, sql_params): """ Returns the keywords as an SQL string, appending SQL parameter values to argument dictionary. """ result = "" for keyword, words in keywords.items(): kw_sql = "" for i, word in enumerate(words): param = add_escape = "" escaped = self.escape(word) if len(escaped) > len(word): add_escape = " ESCAPE '%s'" % ESCAPE_CHAR if keyword.endswith("from") or keyword.endswith("chat"): fields = ["m.author", "m.from_dispname"] param = "author_like%s" % len(sql_params) if keyword.endswith("chat"): fields = ["c.identity", "c.displayname", "c.given_displayname", "c.meta_topic"] param = "chat_like%s" % len(sql_params) items = ["%s LIKE :%s%s" % (f, param, add_escape) for f in fields] sql = " OR ".join(items) sql_params[param] = "%" + word + "%" elif keyword.endswith("date"): # date:2002..2003-11-21 UNIX_EPOCH = datetime.date(1970, 1, 1) sql = "" date_words, dates = [None] * 2, [None] * 2 if ".." not in word: # Single date value given: use strftime matching ymd = map(util.toint, word.split("-")[:3]) while len(ymd) < 3: ymd.append(None) # Ensure 3 values if not any(ymd): # No valid values given: skip continue # continue for i, word in enumerate(words) format, value = "", "" for j, (frm, val) in enumerate(zip("Ymd", ymd)): if val is None: continue # continue for j, (forma.. format += ("-" if format else "") + "%" + frm value += ("-" if value else "") value += "%02d" % val if j else "%04d" % val param = "timestamp_%s" % len(sql_params) temp = "STRFTIME('%s', m.timestamp, 'unixepoch') = :%s" sql = temp % (format, param) sql_params[param] = value else: # Date range given: use timestamp matching date_words = word.split("..", 1) for j, d in filter(lambda x: x[1], enumerate(date_words)): ymd = map(util.toint, filter(None, d.split("-")[:3])) if not ymd or ymd[0] is None: continue # continue for j, d in filter(.. while len(ymd) < 3: ymd.append(None) # Ensure 3 values ymd[0] = max(min(ymd[0], 9999), 1) # Year in 1..9999 # Force month into legal range if ymd[1] is None: ymd[1] = [1, 12][j] else: ymd[1] = max(min(ymd[1], 12), 1) # Month in 1..12 # Force day into legal range day_max = calendar.monthrange(*ymd[:2])[1] if ymd[2] is None: ymd[2] = day_max if j else 1 else: ymd[2] = max(min(ymd[2], day_max), 1) dates[j] = datetime.date(*ymd) for j, d in filter(lambda x: x[1], enumerate(dates)): timestamp = int(util.timedelta_seconds(d - UNIX_EPOCH)) param = "timestamp_%s" % len(sql_params) sql += (" AND " if sql else "") sql += "m.timestamp %s :%s" % ([">=", "<="][j], param) sql_params[param] = timestamp kw_sql += (" OR " if kw_sql else "") + sql if kw_sql: negation = keyword.startswith("-") result += " AND " if result else "" result += "%s(%s)" % ("NOT " if negation else "", kw_sql) return result
def _makeKeywordsSQL(self, keywords, sql_params): """ Returns the keywords as an SQL string, appending SQL parameter values to argument dictionary. """ result = "" for keyword, words in keywords.items(): kw_sql = "" for word in words: param = add_escape = "" escaped = self._escape(word) if len(escaped) > len(word): add_escape = " ESCAPE '%s'" % ESCAPE_CHAR if keyword.endswith("from") or keyword.endswith("chat"): fields = ["m.author", "m.from_dispname"] param = "author_like%s" % len(sql_params) if keyword.endswith("chat"): fields = [ "c.identity", "c.displayname", "c.given_displayname", "c.meta_topic" ] param = "chat_like%s" % len(sql_params) items = [ "%s LIKE :%s%s" % (f, param, add_escape) for f in fields ] sql = " OR ".join(items) sql_params[param] = "%" + word + "%" elif keyword.endswith("date"): # date:2002..2003-11-21 UNIX_EPOCH = datetime.date(1970, 1, 1) sql = "" date_words, dates = [None] * 2, [None] * 2 if ".." not in word: # Single date value given: use strftime matching ymd = list(map(util.to_int, word.split("-")[:3])) while len(ymd) < 3: ymd.append(None) # Ensure 3 values if not any(ymd): # No valid values given: skip continue # continue for word in words format, value = "", "" for j, (frm, val) in enumerate(zip("Ymd", ymd)): if val is None: continue # continue for j, (forma.. format += ("-" if format else "") + "%" + frm value += ("-" if value else "") value += "%02d" % val if j else "%04d" % val param = "timestamp_%s" % len(sql_params) temp = "STRFTIME('%s', m.timestamp, 'unixepoch') = :%s" sql = temp % (format, param) sql_params[param] = value else: # Date range given: use timestamp matching date_words = word.split("..", 1) for i, d in ((i, d) for i, d in enumerate(date_words) if d): parts = filter(None, d.split("-")[:3]) ymd = list(map(util.to_int, parts)) if not ymd or ymd[0] is None: continue # continue for i, d in filter(.. while len(ymd) < 3: ymd.append(None) # Ensure 3 values ymd[0] = max(min(ymd[0], 9999), 1) # Year in 1..9999 # Force month into legal range if ymd[1] is None: ymd[1] = [1, 12][i] else: ymd[1] = max(min(ymd[1], 12), 1) # Month in 1..12 # Force day into legal range day_max = calendar.monthrange(*ymd[:2])[1] if ymd[2] is None: ymd[2] = day_max if i else 1 else: ymd[2] = max(min(ymd[2], day_max), 1) dates[i] = datetime.date(*ymd) for i, d in ((i, d) for i, d in enumerate(dates) if d): timestamp = int(util.timedelta_seconds(d - UNIX_EPOCH)) param = "timestamp_%s" % len(sql_params) sql += (" AND " if sql else "") sql += "m.timestamp %s :%s" % ([">=", "<="][i], param) sql_params[param] = timestamp kw_sql += (" OR " if kw_sql else "") + sql if kw_sql: negation = keyword.startswith("-") result += " AND " if result else "" result += "%s(%s)" % ("NOT " if negation else "", kw_sql) return result