def mail_send(self, from_, to, message):
from_ = normalize_nick(from_, self.aliases)
to = normalize_nick(to, self.aliases)
cursor = self.db.cursor()
cursor.execute(
"INSERT INTO mail (from_nick, to_nick, message, received, sent_at, received_at) VALUES (?, ?, ?, ?, ?, ?)",
(from_, to, message, False, int(datetime.utcnow().timestamp()), None),
)
self.db.commit()
def mail_send(self, from_, to, message):
from_ = normalize_nick(from_, self.aliases)
to = normalize_nick(to, self.aliases)
cursor = self.db.cursor()
cursor.execute(
'INSERT INTO mail (from_nick, to_nick, message, received, sent_at, received_at) VALUES (?, ?, ?, ?, ?, ?)',
(from_, to, message, False,
int(datetime.now(timezone.utc).timestamp()), None))
self.db.commit()
def add_quote(self, channel, timestamp, author, message, commit=True, full_only=False):
raw_author = author
raw_message = message
author = normalize_nick(author, self.aliases)
message = message.rstrip() # remove trailing whitespace from message
word_count = len(message.split())
if timestamp == 0 or len(author) == 0:
return False
self.db.execute("INSERT OR IGNORE INTO channels (channel, seq_id) VALUES (?, ?)", (channel, 0))
seq_id, = self.db.execute("SELECT seq_id FROM channels WHERE channel=?", (channel,)).fetchone()
seq_id += 1 # increment by 1; the id stored in the sequence table will always be the last one used
self.db.execute("UPDATE channels SET seq_id=? WHERE channel=?", (seq_id, channel))
self.db.execute(
"INSERT INTO quotes_full (channel, seq_id, time, raw_author, raw_message, word_count) VALUES (?, ?, ?, ?, ?, ?)",
(channel, seq_id, timestamp, raw_author, raw_message, word_count),
)
# if the message is long enough, wasn't made by an ignored nick, and wasn't an explicit command, add it to the fast table
if word_count >= 5 and author not in self.ignore_nicks and not full_only:
self.db.execute(
"INSERT INTO quotes (channel, seq_id, time, author, message) VALUES (?, ?, ?, ?, ?)",
(channel, seq_id, timestamp, author, message),
)
if commit:
self.db.commit()
return True # return true if the quote was added
def _build_quote_where(self, channel, author=None, year=None, word=None):
query = "channel=?"
params = (channel,)
if year is not None:
time_tuple = year_to_timestamps(year)
if time_tuple is None:
return None
query += " AND time BETWEEN ? AND ?"
params += time_tuple
if author is not None:
author = normalize_nick(author, self.aliases)
query += " AND author=?"
params += (author,)
if word is not None:
word = escape_sql_like(word.lower())
query += " AND message LIKE ? ESCAPE ?"
params += ("%" + word + "%", "\\")
# let's hide some stuff
if channel == "#garachat":
query += " AND time NOT BETWEEN ? AND ?"
params += (1407110400, 1410393599) # 2014-08-04 - 2014-09-10
return query, params
def _build_quote_where(self, channel, author=None, year=None, word=None):
query = 'channel=?'
params = (channel, )
if year is not None:
time_tuple = year_to_timestamps(year)
if time_tuple is None:
return None
query += ' AND time BETWEEN ? AND ?'
params += time_tuple
if author is not None:
author = normalize_nick(author, self.aliases)
query += ' AND author=?'
params += (author, )
if word is not None:
word = escape_sql_like(word.lower())
query += ' AND message LIKE ? ESCAPE ?'
params += ('%' + word + '%', '\\')
# let's hide some stuff
if channel == '#garachat':
query += ' AND time NOT BETWEEN ? AND ?'
params += (1407110400, 1410393599) # 2014-08-04 - 2014-09-10
return query, params
def mail_outbox(self, from_):
from_ = normalize_nick(from_, self.aliases)
cursor = self.db.cursor()
cursor.execute(
"SELECT id, to_nick, message FROM mail WHERE from_nick=? AND received=? ORDER BY id", (from_, False)
)
return ["%i: (%s) %s" % (id, to, msg) for id, to, msg in cursor.fetchall()]
def update_last_seen(self, nick, timestamp=None):
nick = normalize_nick(nick, self.aliases)
timestamp = timestamp if timestamp is not None else int(datetime.utcnow().timestamp())
cursor = self.db.cursor()
cursor.execute("INSERT OR IGNORE INTO nicks (nick) VALUES (?)", (nick,))
cursor.execute("UPDATE nicks SET last_seen=? WHERE nick=?", (timestamp, nick))
self.db.commit()
def mail_outbox(self, from_):
from_ = normalize_nick(from_, self.aliases)
cursor = self.db.cursor()
cursor.execute(
'SELECT id, to_nick, message FROM mail WHERE from_nick=? AND received=? ORDER BY id',
(from_, False))
return [
'%i: (%s) %s' % (id, to, msg) for id, to, msg in cursor.fetchall()
]
def last_seen(self, nick):
alias = normalize_nick(nick, self.aliases)
cursor = self.db.cursor()
cursor.execute("SELECT last_seen FROM nicks WHERE nick=?", (alias,))
row = cursor.fetchone()
if row is None or row[0] is None:
return "%s has never been seen :(" % nick
return "%s was last seen on %s :)" % (nick, datetime.utcfromtimestamp(row[0]).strftime("%Y-%m-%d %H:%M:%S"))
def update_last_seen(self, nick, timestamp=None):
nick = normalize_nick(nick, self.aliases)
timestamp = timestamp if timestamp is not None else int(
datetime.now(timezone.utc).timestamp())
cursor = self.db.cursor()
cursor.execute('INSERT OR IGNORE INTO nicks (nick) VALUES (?)',
(nick, ))
cursor.execute('UPDATE nicks SET last_seen=? WHERE nick=?',
(timestamp, nick))
self.db.commit()
def last_seen(self, nick):
alias = normalize_nick(nick, self.aliases)
cursor = self.db.cursor()
cursor.execute('SELECT last_seen FROM nicks WHERE nick=?', (alias, ))
row = cursor.fetchone()
if row is None or row[0] is None:
return '%s has never been seen :(' % nick
return '%s was last seen on %s :)' % (
nick, datetime.fromtimestamp(
row[0], timezone.utc).strftime('%Y-%m-%d %H:%M:%S UTC'))
def set_current_time(self, nick, utc_offset):
try:
utc_offset = clamp(-12, int(utc_offset), 12)
except:
return "wrong format :("
nick = normalize_nick(nick, self.aliases)
cursor = self.db.cursor()
cursor.execute("INSERT OR IGNORE INTO nicks (nick) VALUES (?)", (nick,))
cursor.execute("UPDATE nicks SET utc_offset=? WHERE nick=?", (utc_offset, nick))
self.db.commit()
return "ok :)"
def quote_by_seq_id(self, channel, seq_id):
quote = self.db.execute(
"SELECT seq_id, time, raw_author, raw_message FROM quotes_full WHERE channel=? AND seq_id=?",
(channel, seq_id),
).fetchone()
if quote is None:
return None
(seq_id, timestamp, author, message) = quote
date = datetime.utcfromtimestamp(timestamp).strftime("%b %d %Y")
return "%s -- %s, %s (%i)" % (message, normalize_nick(author, self.aliases), date, seq_id)
def current_time(self, nick):
normalized_nick = normalize_nick(nick, self.aliases)
cursor = self.db.cursor()
cursor.execute("SELECT utc_offset FROM nicks WHERE nick=?", (normalized_nick,))
row = cursor.fetchone()
if row is None or row[0] is None:
return "no timezone found for %s :(" % nick
offset = row[0]
utc_offset_str = "+%i" % offset if offset >= 0 else str(offset)
tz = timezone(timedelta(hours=offset))
return "it's %s for %s (utc%s)" % (datetime.now(tz).strftime("%I:%M %p"), nick, utc_offset_str)
def quote_by_seq_id(self, channel, seq_id):
quote = self.db.execute(
'SELECT seq_id, time, raw_author, raw_message FROM quotes_full WHERE channel=? AND seq_id=?',
(channel, seq_id)).fetchone()
if quote is None:
return None
(seq_id, timestamp, author, message) = quote
date = datetime.fromtimestamp(timestamp,
timezone.utc).strftime('%b %d %Y')
return '%s -- %s, %s (%i)' % (
message, normalize_nick(author, self.aliases), date, seq_id)
def set_current_time(self, nick, utc_offset):
try:
utc_offset = clamp(-12, int(utc_offset), 12)
except:
return 'wrong format :('
nick = normalize_nick(nick, self.aliases)
cursor = self.db.cursor()
cursor.execute('INSERT OR IGNORE INTO nicks (nick) VALUES (?)',
(nick, ))
cursor.execute('UPDATE nicks SET utc_offset=? WHERE nick=?',
(utc_offset, nick))
self.db.commit()
return 'ok :)'
def current_time(self, nick):
normalized_nick = normalize_nick(nick, self.aliases)
cursor = self.db.cursor()
cursor.execute('SELECT utc_offset FROM nicks WHERE nick=?',
(normalized_nick, ))
row = cursor.fetchone()
if row is None or row[0] is None:
return 'no timezone found for %s :(' % nick
offset = row[0]
utc_offset_str = '+%i' % offset if offset >= 0 else str(offset)
tz = timezone(timedelta(hours=offset))
return 'it\'s %s for %s (utc%s)' % (
datetime.now(tz).strftime('%I:%M %p'), nick, utc_offset_str)
def dump_irssi_log_authors(self, filename):
authors = {}
with open(filename, 'r', encoding='utf-8') as log:
for line in log:
match = re.match(r'^(\d\d):(\d\d)\s<(.+?)>\s(.+)$', line)
if match is None:
continue
author = normalize_nick(match.group(3), self.aliases)
if author not in authors.keys():
authors[author] = 0
authors[author] += 1
return authors
def dump_irssi_log_authors(self, filename):
authors = {}
with open(filename, "r", encoding="utf-8") as log:
for line in log:
match = re.match(r"^(\d\d):(\d\d)\s<.(.+?)>\s(.+)$", line)
if match is None:
continue
author = normalize_nick(match.group(3), self.aliases)
if author not in authors.keys():
authors[author] = 0
authors[author] += 1
return authors
def mail_unread_messages(self, to):
to = normalize_nick(to, self.aliases)
cursor = self.db.cursor()
cursor.execute(
"SELECT from_nick, message, sent_at FROM mail WHERE to_nick=? AND received=? ORDER BY sent_at ASC",
(to, False),
)
messages = [
"%s -- %s, %s" % (msg, from_, datetime.utcfromtimestamp(sent).strftime("%Y-%m-%d %H:%M:%S"))
for from_, msg, sent in cursor.fetchall()
]
now = int(datetime.utcnow().timestamp())
cursor.execute(
"UPDATE mail SET received=?, received_at=? WHERE to_nick=? AND received=?", (True, now, to, False)
)
self.db.commit()
return messages
def mail_unread_messages(self, to):
to = normalize_nick(to, self.aliases)
cursor = self.db.cursor()
cursor.execute(
'SELECT from_nick, message, sent_at FROM mail WHERE to_nick=? AND received=? ORDER BY sent_at ASC',
(to, False))
messages = [
'%s -- %s, %s' %
(msg, from_, datetime.fromtimestamp(
sent, timezone.utc).strftime('%Y-%m-%d %H:%M:%S'))
for from_, msg, sent in cursor.fetchall()
]
now = int(datetime.now(timezone.utc).timestamp())
cursor.execute(
'UPDATE mail SET received=?, received_at=? WHERE to_nick=? AND received=?',
(True, now, to, False))
self.db.commit()
return messages
def add_quote(self,
channel,
timestamp,
author,
message,
commit=True,
full_only=False):
raw_author = author
raw_message = message
author = normalize_nick(author, self.aliases)
message = message.rstrip() # remove trailing whitespace from message
word_count = len(message.split())
if timestamp == 0 or len(author) == 0:
return False
self.db.execute(
'INSERT OR IGNORE INTO channels (channel, seq_id) VALUES (?, ?)',
(channel, 0))
seq_id, = self.db.execute(
'SELECT seq_id FROM channels WHERE channel=?',
(channel, )).fetchone()
seq_id += 1 # increment by 1; the id stored in the sequence table will always be the last one used
self.db.execute('UPDATE channels SET seq_id=? WHERE channel=?',
(seq_id, channel))
self.db.execute(
'INSERT INTO quotes_full (channel, seq_id, time, raw_author, raw_message, word_count) VALUES (?, ?, ?, ?, ?, ?)',
(channel, seq_id, timestamp, raw_author, raw_message, word_count))
# if the message is long enough, wasn't made by an ignored nick, and wasn't an explicit command, add it to the fast table
if word_count >= 5 and author not in self.ignore_nicks and not full_only:
self.db.execute(
'INSERT INTO quotes (channel, seq_id, time, author, message) VALUES (?, ?, ?, ?, ?)',
(channel, seq_id, timestamp, author, message))
if commit:
self.db.commit()
return True # return true if the quote was added
