src/common/logger.py

   1 # -*- coding:utf-8 -*-
   2 ## src/common/logger.py
   3 ##
   4 ## Copyright (C) 2003-2010 Yann Leboulanger <asterix AT lagaule.org>
   5 ## Copyright (C) 2004-2005 Vincent Hanquez <tab AT snarc.org>
   6 ## Copyright (C) 2005-2006 Nikos Kouremenos <kourem AT gmail.com>
   7 ## Copyright (C) 2006 Dimitur Kirov <dkirov AT gmail.com>
   8 ## Copyright (C) 2006-2008 Jean-Marie Traissard <jim AT lapin.org>
   9 ## Copyright (C) 2007 Tomasz Melcer <liori AT exroot.org>
  10 ##                    Julien Pivotto <roidelapluie AT gmail.com>
  11 ##
  12 ## This file is part of Gajim.
  13 ##
  14 ## Gajim is free software; you can redistribute it and/or modify
  15 ## it under the terms of the GNU General Public License as published
  16 ## by the Free Software Foundation; version 3 only.
  17 ##
  18 ## Gajim is distributed in the hope that it will be useful,
  19 ## but WITHOUT ANY WARRANTY; without even the implied warranty of
  20 ## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  21 ## GNU General Public License for more details.
  22 ##
  23 ## You should have received a copy of the GNU General Public License
  24 ## along with Gajim. If not, see <http://www.gnu.org/licenses/>.
  25 ##
  26
  27 """
  28 This module allows to access the on-disk database of logs
  29 """
  30
  31 import os
  32 import sys
  33 import time
  34 import datetime
  35 from gzip import GzipFile
  36 from cStringIO import StringIO
  37
  38 import exceptions
  39 import gajim
  40 import ged
  41
  42 import sqlite3 as sqlite
  43
  44 import configpaths
  45 LOG_DB_PATH = configpaths.gajimpaths['LOG_DB']
  46 LOG_DB_FOLDER, LOG_DB_FILE = os.path.split(LOG_DB_PATH)
  47 CACHE_DB_PATH = configpaths.gajimpaths['CACHE_DB']
  48
  49 import logging
  50 log = logging.getLogger('gajim.c.logger')
  51
  52 class Constants:
  53     def __init__(self):
  54         (
  55                 self.JID_NORMAL_TYPE,
  56                 self.JID_ROOM_TYPE
  57         ) = range(2)
  58
  59         (
  60                 self.KIND_STATUS,
  61                 self.KIND_GCSTATUS,
  62                 self.KIND_GC_MSG,
  63                 self.KIND_SINGLE_MSG_RECV,
  64                 self.KIND_CHAT_MSG_RECV,
  65                 self.KIND_SINGLE_MSG_SENT,
  66                 self.KIND_CHAT_MSG_SENT,
  67                 self.KIND_ERROR
  68         ) = range(8)
  69
  70         (
  71                 self.SHOW_ONLINE,
  72                 self.SHOW_CHAT,
  73                 self.SHOW_AWAY,
  74                 self.SHOW_XA,
  75                 self.SHOW_DND,
  76                 self.SHOW_OFFLINE
  77         ) = range(6)
  78
  79         (
  80                 self.TYPE_AIM,
  81                 self.TYPE_GG,
  82                 self.TYPE_HTTP_WS,
  83                 self.TYPE_ICQ,
  84                 self.TYPE_MSN,
  85                 self.TYPE_QQ,
  86                 self.TYPE_SMS,
  87                 self.TYPE_SMTP,
  88                 self.TYPE_TLEN,
  89                 self.TYPE_YAHOO,
  90                 self.TYPE_NEWMAIL,
  91                 self.TYPE_RSS,
  92                 self.TYPE_WEATHER,
  93                 self.TYPE_MRIM,
  94         ) = range(14)
  95
  96         (
  97                 self.SUBSCRIPTION_NONE,
  98                 self.SUBSCRIPTION_TO,
  99                 self.SUBSCRIPTION_FROM,
 100                 self.SUBSCRIPTION_BOTH,
 101         ) = range(4)
 102
 103 constants = Constants()
 104
 105 class Logger:
 106     def __init__(self):
 107         self.jids_already_in = [] # holds jids that we already have in DB
 108         self.con = None
 109
 110         if not os.path.exists(LOG_DB_PATH):
 111             # this can happen only the first time (the time we create the db)
 112             # db is not created here but in src/common/checks_paths.py
 113             return
 114         self.init_vars()
 115         if not os.path.exists(CACHE_DB_PATH):
 116             # this can happen cache database is not present when gajim is launched
 117             # db will be created in src/common/checks_paths.py
 118             return
 119         self.attach_cache_database()
 120         gajim.ged.register_event_handler('gc-message-received',
 121             ged.POSTCORE, self._nec_gc_message_received)
 122
 123     def close_db(self):
 124         if self.con:
 125             self.con.close()
 126         self.con = None
 127         self.cur = None
 128
 129     def open_db(self):
 130         self.close_db()
 131
 132         # FIXME: sqlite3_open wants UTF8 strings. So a path with
 133         # non-ascii chars doesn't work. See #2812 and
 134         # http://lists.initd.org/pipermail/pysqlite/2005-August/000134.html
 135         back = os.getcwd()
 136         os.chdir(LOG_DB_FOLDER)
 137
 138         # if locked, wait up to 20 sec to unlock
 139         # before raise (hopefully should be enough)
 140
 141         self.con = sqlite.connect(LOG_DB_FILE, timeout=20.0,
 142                 isolation_level='IMMEDIATE')
 143         os.chdir(back)
 144         self.cur = self.con.cursor()
 145         self.set_synchronous(False)
 146
 147     def attach_cache_database(self):
 148         try:
 149             self.cur.execute("ATTACH DATABASE '%s' AS cache" % CACHE_DB_PATH)
 150         except sqlite.Error, e:
 151             log.debug("Failed to attach cache database: %s" % str(e))
 152
 153     def set_synchronous(self, sync):
 154         try:
 155             if sync:
 156                 self.cur.execute("PRAGMA synchronous = NORMAL")
 157             else:
 158                 self.cur.execute("PRAGMA synchronous = OFF")
 159         except sqlite.Error, e:
 160             log.debug("Failed to set_synchronous(%s): %s" % (sync, str(e)))
 161
 162     def init_vars(self):
 163         self.open_db()
 164         self.get_jids_already_in_db()
 165
 166     def simple_commit(self, sql_to_commit):
 167         """
 168         Helper to commit
 169         """
 170         self.cur.execute(sql_to_commit)
 171         try:
 172             self.con.commit()
 173         except sqlite.OperationalError, e:
 174             print >> sys.stderr, str(e)
 175
 176     def get_jids_already_in_db(self):
 177         try:
 178             self.cur.execute('SELECT jid FROM jids')
 179             # list of tupples: [(u'aaa@bbb',), (u'cc@dd',)]
 180             rows = self.cur.fetchall()
 181         except sqlite.DatabaseError:
 182             raise exceptions.DatabaseMalformed
 183         self.jids_already_in = []
 184         for row in rows:
 185             # row[0] is first item of row (the only result here, the jid)
 186             if row[0] == '':
 187                 # malformed jid, ignore line
 188                 pass
 189             else:
 190                 self.jids_already_in.append(row[0])
 191
 192     def get_jids_in_db(self):
 193         return self.jids_already_in
 194
 195     def jid_is_from_pm(self, jid):
 196         """
 197         If jid is gajim@conf/nkour it's likely a pm one, how we know gajim@conf
 198         is not a normal guy and nkour is not his resource?  we ask if gajim@conf
 199         is already in jids (with type room jid) this fails if user disables
 200         logging for room and only enables for pm (so higly unlikely) and if we
 201         fail we do not go chaos (user will see the first pm as if it was message
 202         in room's public chat) and after that all okay
 203         """
 204         if jid.find('/') > -1:
 205             possible_room_jid = jid.split('/', 1)[0]
 206             return self.jid_is_room_jid(possible_room_jid)
 207         else:
 208             # it's not a full jid, so it's not a pm one
 209             return False
 210
 211     def jid_is_room_jid(self, jid):
 212         self.cur.execute('SELECT jid_id FROM jids WHERE jid=?  AND type=?',
 213                 (jid, constants.JID_ROOM_TYPE))
 214         row = self.cur.fetchone()
 215         if row is None:
 216             return False
 217         else:
 218             return True
 219
 220     def get_jid_id(self, jid, typestr=None):
 221         """
 222         jids table has jid and jid_id logs table has log_id, jid_id,
 223         contact_name, time, kind, show, message so to ask logs we need jid_id
 224         that matches our jid in jids table this method wants jid and returns the
 225         jid_id for later sql-ing on logs typestr can be 'ROOM' or anything else
 226         depending on the type of JID and is only needed to be specified when the
 227         JID is new in DB
 228         """
 229         if jid.find('/') != -1: # if it has a /
 230             jid_is_from_pm = self.jid_is_from_pm(jid)
 231             if not jid_is_from_pm: # it's normal jid with resource
 232                 jid = jid.split('/', 1)[0] # remove the resource
 233         if jid in self.jids_already_in: # we already have jids in DB
 234             self.cur.execute('SELECT jid_id FROM jids WHERE jid=?', [jid])
 235             row = self.cur.fetchone()
 236             if row:
 237                 return row[0]
 238         # oh! a new jid :), we add it now
 239         if typestr == 'ROOM':
 240             typ = constants.JID_ROOM_TYPE
 241         else:
 242             typ = constants.JID_NORMAL_TYPE
 243         try:
 244             self.cur.execute('INSERT INTO jids (jid, type) VALUES (?, ?)', (jid,
 245                     typ))
 246             self.con.commit()
 247         except sqlite.IntegrityError, e:
 248             # Jid already in DB, maybe added by another instance. re-read DB
 249             self.get_jids_already_in_db()
 250             return self.get_jid_id(jid, typestr)
 251         except sqlite.OperationalError, e:
 252             raise exceptions.PysqliteOperationalError(str(e))
 253         jid_id = self.cur.lastrowid
 254         self.jids_already_in.append(jid)
 255         return jid_id
 256
 257     def convert_human_values_to_db_api_values(self, kind, show):
 258         """
 259         Convert from string style to constant ints for db
 260         """
 261         if kind == 'status':
 262             kind_col = constants.KIND_STATUS
 263         elif kind == 'gcstatus':
 264             kind_col = constants.KIND_GCSTATUS
 265         elif kind == 'gc_msg':
 266             kind_col = constants.KIND_GC_MSG
 267         elif kind == 'single_msg_recv':
 268             kind_col = constants.KIND_SINGLE_MSG_RECV
 269         elif kind == 'single_msg_sent':
 270             kind_col = constants.KIND_SINGLE_MSG_SENT
 271         elif kind == 'chat_msg_recv':
 272             kind_col = constants.KIND_CHAT_MSG_RECV
 273         elif kind == 'chat_msg_sent':
 274             kind_col = constants.KIND_CHAT_MSG_SENT
 275         elif kind == 'error':
 276             kind_col = constants.KIND_ERROR
 277
 278         if show == 'online':
 279             show_col = constants.SHOW_ONLINE
 280         elif show == 'chat':
 281             show_col = constants.SHOW_CHAT
 282         elif show == 'away':
 283             show_col = constants.SHOW_AWAY
 284         elif show == 'xa':
 285             show_col = constants.SHOW_XA
 286         elif show == 'dnd':
 287             show_col = constants.SHOW_DND
 288         elif show == 'offline':
 289             show_col = constants.SHOW_OFFLINE
 290         elif show is None:
 291             show_col = None
 292         else: # invisible in GC when someone goes invisible
 293             # it's a RFC violation .... but we should not crash
 294             show_col = 'UNKNOWN'
 295
 296         return kind_col, show_col
 297
 298     def convert_human_transport_type_to_db_api_values(self, type_):
 299         """
 300         Convert from string style to constant ints for db
 301         """
 302         if type_ == 'aim':
 303             return constants.TYPE_AIM
 304         if type_ == 'gadu-gadu':
 305             return constants.TYPE_GG
 306         if type_ == 'http-ws':
 307             return constants.TYPE_HTTP_WS
 308         if type_ == 'icq':
 309             return constants.TYPE_ICQ
 310         if type_ == 'msn':
 311             return constants.TYPE_MSN
 312         if type_ == 'qq':
 313             return constants.TYPE_QQ
 314         if type_ == 'sms':
 315             return constants.TYPE_SMS
 316         if type_ == 'smtp':
 317             return constants.TYPE_SMTP
 318         if type_ in ('tlen', 'x-tlen'):
 319             return constants.TYPE_TLEN
 320         if type_ == 'yahoo':
 321             return constants.TYPE_YAHOO
 322         if type_ == 'newmail':
 323             return constants.TYPE_NEWMAIL
 324         if type_ == 'rss':
 325             return constants.TYPE_RSS
 326         if type_ == 'weather':
 327             return constants.TYPE_WEATHER
 328         if type_ == 'mrim':
 329             return constants.TYPE_MRIM
 330         return None
 331
 332     def convert_api_values_to_human_transport_type(self, type_id):
 333         """
 334         Convert from constant ints for db to string style
 335         """
 336         if type_id == constants.TYPE_AIM:
 337             return 'aim'
 338         if type_id == constants.TYPE_GG:
 339             return 'gadu-gadu'
 340         if type_id == constants.TYPE_HTTP_WS:
 341             return 'http-ws'
 342         if type_id == constants.TYPE_ICQ:
 343             return 'icq'
 344         if type_id == constants.TYPE_MSN:
 345             return 'msn'
 346         if type_id == constants.TYPE_QQ:
 347             return 'qq'
 348         if type_id == constants.TYPE_SMS:
 349             return 'sms'
 350         if type_id == constants.TYPE_SMTP:
 351             return 'smtp'
 352         if type_id == constants.TYPE_TLEN:
 353             return 'tlen'
 354         if type_id == constants.TYPE_YAHOO:
 355             return 'yahoo'
 356         if type_id == constants.TYPE_NEWMAIL:
 357             return 'newmail'
 358         if type_id == constants.TYPE_RSS:
 359             return 'rss'
 360         if type_id == constants.TYPE_WEATHER:
 361             return 'weather'
 362         if type_id == constants.TYPE_MRIM:
 363             return 'mrim'
 364
 365     def convert_human_subscription_values_to_db_api_values(self, sub):
 366         """
 367         Convert from string style to constant ints for db
 368         """
 369         if sub == 'none':
 370             return constants.SUBSCRIPTION_NONE
 371         if sub == 'to':
 372             return constants.SUBSCRIPTION_TO
 373         if sub == 'from':
 374             return constants.SUBSCRIPTION_FROM
 375         if sub == 'both':
 376             return constants.SUBSCRIPTION_BOTH
 377
 378     def convert_db_api_values_to_human_subscription_values(self, sub):
 379         """
 380         Convert from constant ints for db to string style
 381         """
 382         if sub == constants.SUBSCRIPTION_NONE:
 383             return 'none'
 384         if sub == constants.SUBSCRIPTION_TO:
 385             return 'to'
 386         if sub == constants.SUBSCRIPTION_FROM:
 387             return 'from'
 388         if sub == constants.SUBSCRIPTION_BOTH:
 389             return 'both'
 390
 391     def commit_to_db(self, values, write_unread=False):
 392         sql = '''INSERT INTO logs (jid_id, contact_name, time, kind, show,
 393                 message, subject) VALUES (?, ?, ?, ?, ?, ?, ?)'''
 394         try:
 395             self.cur.execute(sql, values)
 396         except sqlite.DatabaseError:
 397             raise exceptions.DatabaseMalformed
 398         except sqlite.OperationalError, e:
 399             raise exceptions.PysqliteOperationalError(str(e))
 400         message_id = None
 401         try:
 402             self.con.commit()
 403             if write_unread:
 404                 message_id = self.cur.lastrowid
 405         except sqlite.OperationalError, e:
 406             print >> sys.stderr, str(e)
 407         if message_id:
 408             self.insert_unread_events(message_id, values[0])
 409         return message_id
 410
 411     def insert_unread_events(self, message_id, jid_id):
 412         """
 413         Add unread message with id: message_id
 414         """
 415         sql = 'INSERT INTO unread_messages VALUES (%d, %d, 0)' % (message_id,
 416                 jid_id)
 417         self.simple_commit(sql)
 418
 419     def set_read_messages(self, message_ids):
 420         """
 421         Mark all messages with ids in message_ids as read
 422         """
 423         ids = ','.join([str(i) for i in message_ids])
 424         sql = 'DELETE FROM unread_messages WHERE message_id IN (%s)' % ids
 425         self.simple_commit(sql)
 426
 427     def set_shown_unread_msgs(self, msg_id):
 428         """
 429         Mark unread message as shown un GUI
 430         """
 431         sql = 'UPDATE unread_messages SET shown = 1 where message_id = %s' % \
 432                 msg_id
 433         self.simple_commit(sql)
 434
 435     def reset_shown_unread_messages(self):
 436         """
 437         Set shown field to False in unread_messages table
 438         """
 439         sql = 'UPDATE unread_messages SET shown = 0'
 440         self.simple_commit(sql)
 441
 442     def get_unread_msgs(self):
 443         """
 444         Get all unread messages
 445         """
 446         all_messages = []
 447         try:
 448             self.cur.execute(
 449                     'SELECT message_id, shown from unread_messages')
 450             results = self.cur.fetchall()
 451         except Exception:
 452             pass
 453         for message in results:
 454             msg_id = message[0]
 455             shown = message[1]
 456             # here we get infos for that message, and related jid from jids table
 457             # do NOT change order of SELECTed things, unless you change function(s)
 458             # that called this function
 459             self.cur.execute('''
 460                     SELECT logs.log_line_id, logs.message, logs.time, logs.subject,
 461                     jids.jid
 462                     FROM logs, jids
 463                     WHERE logs.log_line_id = %d AND logs.jid_id = jids.jid_id
 464                     ''' % msg_id
 465                     )
 466             results = self.cur.fetchall()
 467             if len(results) == 0:
 468                 # Log line is no more in logs table. remove it from unread_messages
 469                 self.set_read_messages([msg_id])
 470                 continue
 471             all_messages.append(results[0] + (shown,))
 472         return all_messages
 473
 474     def write(self, kind, jid, message=None, show=None, tim=None, subject=None):
 475         """
 476         Write a row (status, gcstatus, message etc) to logs database
 477
 478         kind can be status, gcstatus, gc_msg, (we only recv for those 3),
 479         single_msg_recv, chat_msg_recv, chat_msg_sent, single_msg_sent we cannot
 480         know if it is pm or normal chat message, we try to guess see
 481         jid_is_from_pm()
 482
 483         We analyze jid and store it as follows:
 484                 jids.jid text column will hold JID if TC-related, room_jid if GC-related,
 485                 ROOM_JID/nick if pm-related.
 486         """
 487
 488         if self.jids_already_in == []: # only happens if we just created the db
 489             self.open_db()
 490
 491         contact_name_col = None # holds nickname for kinds gcstatus, gc_msg
 492         # message holds the message unless kind is status or gcstatus,
 493         # then it holds status message
 494         message_col = message
 495         subject_col = subject
 496         if tim:
 497             time_col = int(float(time.mktime(tim)))
 498         else:
 499             time_col = int(float(time.time()))
 500
 501         kind_col, show_col = self.convert_human_values_to_db_api_values(kind,
 502                 show)
 503
 504         write_unread = False
 505
 506         # now we may have need to do extra care for some values in columns
 507         if kind == 'status': # we store (not None) time, jid, show, msg
 508             # status for roster items
 509             try:
 510                 jid_id = self.get_jid_id(jid)
 511             except exceptions.PysqliteOperationalError, e:
 512                 raise exceptions.PysqliteOperationalError(str(e))
 513             if show is None: # show is None (xmpp), but we say that 'online'
 514                 show_col = constants.SHOW_ONLINE
 515
 516         elif kind == 'gcstatus':
 517             # status in ROOM (for pm status see status)
 518             if show is None: # show is None (xmpp), but we say that 'online'
 519                 show_col = constants.SHOW_ONLINE
 520             jid, nick = jid.split('/', 1)
 521             try:
 522                 # re-get jid_id for the new jid
 523                 jid_id = self.get_jid_id(jid, 'ROOM')
 524             except exceptions.PysqliteOperationalError, e:
 525                 raise exceptions.PysqliteOperationalError(str(e))
 526             contact_name_col = nick
 527
 528         elif kind == 'gc_msg':
 529             if jid.find('/') != -1: # if it has a /
 530                 jid, nick = jid.split('/', 1)
 531             else:
 532                 # it's server message f.e. error message
 533                 # when user tries to ban someone but he's not allowed to
 534                 nick = None
 535             try:
 536                 # re-get jid_id for the new jid
 537                 jid_id = self.get_jid_id(jid, 'ROOM')
 538             except exceptions.PysqliteOperationalError, e:
 539                 raise exceptions.PysqliteOperationalError(str(e))
 540             contact_name_col = nick
 541         else:
 542             try:
 543                 jid_id = self.get_jid_id(jid)
 544             except exceptions.PysqliteOperationalError, e:
 545                 raise exceptions.PysqliteOperationalError(str(e))
 546             if kind == 'chat_msg_recv':
 547                 if not self.jid_is_from_pm(jid):
 548                     # Save in unread table only if it's not a pm
 549                     write_unread = True
 550
 551         if show_col == 'UNKNOWN': # unknown show, do not log
 552             return
 553
 554         values = (jid_id, contact_name_col, time_col, kind_col, show_col,
 555                 message_col, subject_col)
 556         return self.commit_to_db(values, write_unread)
 557
 558     def get_last_conversation_lines(self, jid, restore_how_many_rows,
 559                     pending_how_many, timeout, account):
 560         """
 561         Accept how many rows to restore and when to time them out (in minutes)
 562         (mark them as too old) and number of messages that are in queue and are
 563         already logged but pending to be viewed, returns a list of tupples
 564         containg time, kind, message, list with empty tupple if nothing found to
 565         meet our demands
 566         """
 567         try:
 568             self.get_jid_id(jid)
 569         except exceptions.PysqliteOperationalError, e:
 570             # Error trying to create a new jid_id. This means there is no log
 571             return []
 572         where_sql = self._build_contact_where(account, jid)
 573
 574         now = int(float(time.time()))
 575         timed_out = now - (timeout * 60) # before that they are too old
 576         # so if we ask last 5 lines and we have 2 pending we get
 577         # 3 - 8 (we avoid the last 2 lines but we still return 5 asked)
 578         try:
 579             self.cur.execute('''
 580                     SELECT time, kind, message FROM logs
 581                     WHERE (%s) AND kind IN (%d, %d, %d, %d, %d) AND time > %d
 582                     ORDER BY time DESC LIMIT %d OFFSET %d
 583                     ''' % (where_sql, constants.KIND_SINGLE_MSG_RECV,
 584                             constants.KIND_CHAT_MSG_RECV, constants.KIND_SINGLE_MSG_SENT,
 585                             constants.KIND_CHAT_MSG_SENT, constants.KIND_ERROR,
 586                             timed_out, restore_how_many_rows, pending_how_many)
 587                     )
 588
 589             results = self.cur.fetchall()
 590         except sqlite.DatabaseError:
 591             raise exceptions.DatabaseMalformed
 592         results.reverse()
 593         return results
 594
 595     def get_unix_time_from_date(self, year, month, day):
 596         # year (fe 2005), month (fe 11), day (fe 25)
 597         # returns time in seconds for the second that starts that date since epoch
 598         # gimme unixtime from year month day:
 599         d = datetime.date(year, month, day)
 600         local_time = d.timetuple() # time tupple (compat with time.localtime())
 601         # we have time since epoch baby :)
 602         start_of_day = int(time.mktime(local_time))
 603         return start_of_day
 604
 605     def get_conversation_for_date(self, jid, year, month, day, account):
 606         """
 607         Return contact_name, time, kind, show, message, subject
 608
 609         For each row in a list of tupples, returns list with empty tupple if we
 610         found nothing to meet our demands
 611         """
 612         try:
 613             self.get_jid_id(jid)
 614         except exceptions.PysqliteOperationalError, e:
 615             # Error trying to create a new jid_id. This means there is no log
 616             return []
 617         where_sql = self._build_contact_where(account, jid)
 618
 619         start_of_day = self.get_unix_time_from_date(year, month, day)
 620         seconds_in_a_day = 86400 # 60 * 60 * 24
 621         last_second_of_day = start_of_day + seconds_in_a_day - 1
 622
 623         self.cur.execute('''
 624                 SELECT contact_name, time, kind, show, message, subject FROM logs
 625                 WHERE (%s)
 626                 AND time BETWEEN %d AND %d
 627                 ORDER BY time
 628                 ''' % (where_sql, start_of_day, last_second_of_day))
 629
 630         results = self.cur.fetchall()
 631         return results
 632
 633     def get_search_results_for_query(self, jid, query, account):
 634         """
 635         Returns contact_name, time, kind, show, message
 636
 637         For each row in a list of tupples, returns list with empty tupple if we
 638         found nothing to meet our demands
 639         """
 640         try:
 641             self.get_jid_id(jid)
 642         except exceptions.PysqliteOperationalError, e:
 643             # Error trying to create a new jid_id. This means there is no log
 644             return []
 645
 646         if False: # query.startswith('SELECT '): # it's SQL query (FIXME)
 647             try:
 648                 self.cur.execute(query)
 649             except sqlite.OperationalError, e:
 650                 results = [('', '', '', '', str(e))]
 651                 return results
 652
 653         else: # user just typed something, we search in message column
 654             where_sql = self._build_contact_where(account, jid)
 655             like_sql = '%' + query.replace("'", "''") + '%'
 656             self.cur.execute('''
 657                     SELECT contact_name, time, kind, show, message, subject FROM logs
 658                     WHERE (%s) AND message LIKE '%s'
 659                     ORDER BY time
 660                     ''' % (where_sql, like_sql))
 661
 662         results = self.cur.fetchall()
 663         return results
 664
 665     def get_days_with_logs(self, jid, year, month, max_day, account):
 666         """
 667         Return the list of days that have logs (not status messages)
 668         """
 669         try:
 670             self.get_jid_id(jid)
 671         except exceptions.PysqliteOperationalError, e:
 672             # Error trying to create a new jid_id. This means there is no log
 673             return []
 674         days_with_logs = []
 675         where_sql = self._build_contact_where(account, jid)
 676
 677         # First select all date of month whith logs we want
 678         start_of_month = self.get_unix_time_from_date(year, month, 1)
 679         seconds_in_a_day = 86400 # 60 * 60 * 24
 680         last_second_of_month = start_of_month + (seconds_in_a_day * max_day) - 1
 681
 682         # Select times and 'floor' them to time 0:00
 683         # (by dividing, they are integers)
 684         # and take only one of the same values (distinct)
 685         # Now we have timestamps of time 0:00 of every day with logs
 686         self.cur.execute('''
 687                 SELECT DISTINCT time/(86400)*86400 FROM logs
 688                 WHERE (%s)
 689                 AND time BETWEEN %d AND %d
 690                 AND kind NOT IN (%d, %d)
 691                 ORDER BY time
 692                 ''' % (where_sql, start_of_month, last_second_of_month,
 693                 constants.KIND_STATUS, constants.KIND_GCSTATUS))
 694         result = self.cur.fetchall()
 695
 696         # convert timestamps to day of month
 697         for line in result:
 698             days_with_logs[0:0]=[time.gmtime(line[0])[2]]
 699
 700         return days_with_logs
 701
 702     def get_last_date_that_has_logs(self, jid, account=None, is_room=False):
 703         """
 704         Return last time (in seconds since EPOCH) for which we had logs
 705         (excluding statuses)
 706         """
 707         where_sql = ''
 708         if not is_room:
 709             where_sql = self._build_contact_where(account, jid)
 710         else:
 711             try:
 712                 jid_id = self.get_jid_id(jid, 'ROOM')
 713             except exceptions.PysqliteOperationalError, e:
 714                 # Error trying to create a new jid_id. This means there is no log
 715                 return None
 716             where_sql = 'jid_id = %s' % jid_id
 717         self.cur.execute('''
 718                 SELECT MAX(time) FROM logs
 719                 WHERE (%s)
 720                 AND kind NOT IN (%d, %d)
 721                 ''' % (where_sql, constants.KIND_STATUS, constants.KIND_GCSTATUS))
 722
 723         results = self.cur.fetchone()
 724         if results is not None:
 725             result = results[0]
 726         else:
 727             result = None
 728         return result
 729
 730     def get_room_last_message_time(self, jid):
 731         """
 732         Return FASTLY last time (in seconds since EPOCH) for which we had logs
 733         for that room from rooms_last_message_time table
 734         """
 735         try:
 736             jid_id = self.get_jid_id(jid, 'ROOM')
 737         except exceptions.PysqliteOperationalError, e:
 738             # Error trying to create a new jid_id. This means there is no log
 739             return None
 740         where_sql = 'jid_id = %s' % jid_id
 741         self.cur.execute('''
 742                 SELECT time FROM rooms_last_message_time
 743                 WHERE (%s)
 744                 ''' % (where_sql))
 745
 746         results = self.cur.fetchone()
 747         if results is not None:
 748             result = results[0]
 749         else:
 750             result = None
 751         return result
 752
 753     def set_room_last_message_time(self, jid, time):
 754         """
 755         Set last time (in seconds since EPOCH) for which we had logs for that
 756         room in rooms_last_message_time table
 757         """
 758         jid_id = self.get_jid_id(jid, 'ROOM')
 759         # jid_id is unique in this table, create or update :
 760         sql = 'REPLACE INTO rooms_last_message_time VALUES (%d, %d)' % \
 761                 (jid_id, time)
 762         self.simple_commit(sql)
 763
 764     def _build_contact_where(self, account, jid):
 765         """
 766         Build the where clause for a jid, including metacontacts jid(s) if any
 767         """
 768         where_sql = ''
 769         # will return empty list if jid is not associated with
 770         # any metacontacts
 771         family = gajim.contacts.get_metacontacts_family(account, jid)
 772         if family:
 773             for user in family:
 774                 try:
 775                     jid_id = self.get_jid_id(user['jid'])
 776                 except exceptions.PysqliteOperationalError, e:
 777                     continue
 778                 where_sql += 'jid_id = %s' % jid_id
 779                 if user != family[-1]:
 780                     where_sql += ' OR '
 781         else: # if jid was not associated with metacontacts
 782             jid_id = self.get_jid_id(jid)
 783             where_sql = 'jid_id = %s' % jid_id
 784         return where_sql
 785
 786     def save_transport_type(self, jid, type_):
 787         """
 788         Save the type of the transport in DB
 789         """
 790         type_id = self.convert_human_transport_type_to_db_api_values(type_)
 791         if not type_id:
 792             # unknown type
 793             return
 794         self.cur.execute(
 795                 'SELECT type from transports_cache WHERE transport = "%s"' % jid)
 796         results = self.cur.fetchall()
 797         if results:
 798             result = results[0][0]
 799             if result == type_id:
 800                 return
 801             sql = 'UPDATE transports_cache SET type = %d WHERE transport = "%s"' %\
 802                     (type_id, jid)
 803             self.simple_commit(sql)
 804             return
 805         sql = 'INSERT INTO transports_cache VALUES ("%s", %d)' % (jid, type_id)
 806         self.simple_commit(sql)
 807
 808     def get_transports_type(self):
 809         """
 810         Return all the type of the transports in DB
 811         """
 812         self.cur.execute(
 813                 'SELECT * from transports_cache')
 814         results = self.cur.fetchall()
 815         if not results:
 816             return {}
 817         answer = {}
 818         for result in results:
 819             answer[result[0]] = self.convert_api_values_to_human_transport_type(
 820                     result[1])
 821         return answer
 822
 823     # A longer note here:
 824     # The database contains a blob field. Pysqlite seems to need special care for
 825     # such fields.
 826     # When storing, we need to convert string into buffer object (1).
 827     # When retrieving, we need to convert it back to a string to decompress it.
 828     # (2)
 829     # GzipFile needs a file-like object, StringIO emulates file for plain strings
 830     def iter_caps_data(self):
 831         """
 832         Iterate over caps cache data stored in the database
 833
 834         The iterator values are pairs of (node, ver, ext, identities, features):
 835         identities == {'category':'foo', 'type':'bar', 'name':'boo'},
 836         features being a list of feature namespaces.
 837         """
 838         # get data from table
 839         # the data field contains binary object (gzipped data), this is a hack
 840         # to get that data without trying to convert it to unicode
 841         try:
 842             self.cur.execute('SELECT hash_method, hash, data FROM caps_cache;')
 843         except sqlite.OperationalError:
 844             # might happen when there's no caps_cache table yet
 845             # -- there's no data to read anyway then
 846             return
 847
 848         # list of corrupted entries that will be removed
 849         to_be_removed = []
 850         for hash_method, hash_, data in self.cur:
 851             # for each row: unpack the data field
 852             # (format: (category, type, name, category, type, name, ...
 853             #   ..., 'FEAT', feature1, feature2, ...).join(' '))
 854             # NOTE: if there's a need to do more gzip, put that to a function
 855             try:
 856                 data = GzipFile(fileobj=StringIO(str(data))).read().decode(
 857                         'utf-8').split('\0')
 858             except IOError:
 859                 # This data is corrupted. It probably contains non-ascii chars
 860                 to_be_removed.append((hash_method, hash_))
 861                 continue
 862             i = 0
 863             identities = list()
 864             features = list()
 865             while i < (len(data) - 3) and data[i] != 'FEAT':
 866                 category = data[i]
 867                 type_ = data[i + 1]
 868                 lang = data[i + 2]
 869                 name = data[i + 3]
 870                 identities.append({'category': category, 'type': type_,
 871                         'xml:lang': lang, 'name': name})
 872                 i += 4
 873             i+=1
 874             while i < len(data):
 875                 features.append(data[i])
 876                 i += 1
 877
 878             # yield the row
 879             yield hash_method, hash_, identities, features
 880         for hash_method, hash_ in to_be_removed:
 881             sql = '''DELETE FROM caps_cache WHERE hash_method = "%s" AND
 882                     hash = "%s"''' % (hash_method, hash_)
 883             self.simple_commit(sql)
 884
 885     def add_caps_entry(self, hash_method, hash_, identities, features):
 886         data = []
 887         for identity in identities:
 888             # there is no FEAT category
 889             if identity['category'] == 'FEAT':
 890                 return
 891             data.extend((identity.get('category'), identity.get('type', ''),
 892                     identity.get('xml:lang', ''), identity.get('name', '')))
 893         data.append('FEAT')
 894         data.extend(features)
 895         data = '\0'.join(data)
 896         # if there's a need to do more gzip, put that to a function
 897         string = StringIO()
 898         gzip = GzipFile(fileobj=string, mode='w')
 899         data = data.encode('utf-8') # the gzip module can't handle unicode objects
 900         gzip.write(data)
 901         gzip.close()
 902         data = string.getvalue()
 903         self.cur.execute('''
 904                 INSERT INTO caps_cache ( hash_method, hash, data, last_seen )
 905                 VALUES (?, ?, ?, ?);
 906                 ''', (hash_method, hash_, buffer(data), int(time.time())))
 907         # (1) -- note above
 908         try:
 909             self.con.commit()
 910         except sqlite.OperationalError, e:
 911             print >> sys.stderr, str(e)
 912
 913     def update_caps_time(self, method, hash_):
 914         sql = '''UPDATE caps_cache SET last_seen = %d
 915                 WHERE hash_method = "%s" and hash = "%s"''' % \
 916                 (int(time.time()), method, hash_)
 917         self.simple_commit(sql)
 918
 919     def clean_caps_table(self):
 920         """
 921         Remove caps which was not seen for 3 months
 922         """
 923         sql = '''DELETE FROM caps_cache WHERE last_seen < %d''' % \
 924                 int(time.time() - 3*30*24*3600)
 925         self.simple_commit(sql)
 926
 927     def replace_roster(self, account_name, roster_version, roster):
 928         """
 929         Replace current roster in DB by a new one
 930
 931         accout_name is the name of the account to change.
 932         roster_version is the version of the new roster.
 933         roster is the new version.
 934         """
 935         # First we must reset roster_version value to ensure that the server
 936         # sends back all the roster at the next connexion if the replacement
 937         # didn't work properly.
 938         gajim.config.set_per('accounts', account_name, 'roster_version', '')
 939
 940         account_jid = gajim.get_jid_from_account(account_name)
 941         account_jid_id = self.get_jid_id(account_jid)
 942
 943         # Delete old roster
 944         self.remove_roster(account_jid)
 945
 946         # Fill roster tables with the new roster
 947         for jid in roster:
 948             self.add_or_update_contact(account_jid, jid, roster[jid]['name'],
 949                     roster[jid]['subscription'], roster[jid]['ask'],
 950                     roster[jid]['groups'], commit=False)
 951         self.con.commit()
 952
 953         # At this point, we are sure the replacement works properly so we can
 954         # set the new roster_version value.
 955         gajim.config.set_per('accounts', account_name, 'roster_version',
 956             roster_version)
 957
 958     def del_contact(self, account_jid, jid):
 959         """
 960         Remove jid from account_jid roster
 961         """
 962         try:
 963             account_jid_id = self.get_jid_id(account_jid)
 964             jid_id = self.get_jid_id(jid)
 965         except exceptions.PysqliteOperationalError, e:
 966             raise exceptions.PysqliteOperationalError(str(e))
 967         self.cur.execute(
 968                 'DELETE FROM roster_group WHERE account_jid_id=? AND jid_id=?',
 969                 (account_jid_id, jid_id))
 970         self.cur.execute(
 971                 'DELETE FROM roster_entry WHERE account_jid_id=? AND jid_id=?',
 972                 (account_jid_id, jid_id))
 973         self.con.commit()
 974
 975     def add_or_update_contact(self, account_jid, jid, name, sub, ask, groups,
 976     commit=True):
 977         """
 978         Add or update a contact from account_jid roster
 979         """
 980         if sub == 'remove':
 981             self.del_contact(account_jid, jid)
 982             return
 983
 984         try:
 985             account_jid_id = self.get_jid_id(account_jid)
 986             jid_id = self.get_jid_id(jid)
 987         except exceptions.PysqliteOperationalError, e:
 988             raise exceptions.PysqliteOperationalError(str(e))
 989
 990         # Update groups information
 991         # First we delete all previous groups information
 992         self.cur.execute(
 993                 'DELETE FROM roster_group WHERE account_jid_id=? AND jid_id=?',
 994                 (account_jid_id, jid_id))
 995         # Then we add all new groups information
 996         for group in groups:
 997             self.cur.execute('INSERT INTO roster_group VALUES(?, ?, ?)',
 998                     (account_jid_id, jid_id, group))
 999
1000         if name is None:
1001             name = ''
1002
1003         self.cur.execute('REPLACE INTO roster_entry VALUES(?, ?, ?, ?, ?)',
1004                 (account_jid_id, jid_id, name,
1005                 self.convert_human_subscription_values_to_db_api_values(sub),
1006                 bool(ask)))
1007         if commit:
1008             self.con.commit()
1009
1010     def get_roster(self, account_jid):
1011         """
1012         Return the accound_jid roster in NonBlockingRoster format
1013         """
1014         data = {}
1015         account_jid_id = self.get_jid_id(account_jid)
1016
1017         # First we fill data with roster_entry informations
1018         self.cur.execute('''
1019                 SELECT j.jid, re.jid_id, re.name, re.subscription, re.ask
1020                 FROM roster_entry re, jids j
1021                 WHERE re.account_jid_id=? AND j.jid_id=re.jid_id''', (account_jid_id,))
1022         for jid, jid_id, name, subscription, ask in self.cur:
1023             data[jid] = {}
1024             if name:
1025                 data[jid]['name'] = name
1026             else:
1027                 data[jid]['name'] = None
1028             data[jid]['subscription'] = \
1029                     self.convert_db_api_values_to_human_subscription_values(
1030                     subscription)
1031             data[jid]['groups'] = []
1032             data[jid]['resources'] = {}
1033             if ask:
1034                 data[jid]['ask'] = 'subscribe'
1035             else:
1036                 data[jid]['ask'] = None
1037             data[jid]['id'] = jid_id
1038
1039         # Then we add group for roster entries
1040         for jid in data:
1041             self.cur.execute('''
1042                     SELECT group_name FROM roster_group
1043                     WHERE account_jid_id=? AND jid_id=?''',
1044                     (account_jid_id, data[jid]['id']))
1045             for (group_name,) in self.cur:
1046                 data[jid]['groups'].append(group_name)
1047             del data[jid]['id']
1048
1049         return data
1050
1051     def remove_roster(self, account_jid):
1052         """
1053         Remove all entry from account_jid roster
1054         """
1055         account_jid_id = self.get_jid_id(account_jid)
1056
1057         self.cur.execute('DELETE FROM roster_entry WHERE account_jid_id=?',
1058                 (account_jid_id,))
1059         self.cur.execute('DELETE FROM roster_group WHERE account_jid_id=?',
1060                 (account_jid_id,))
1061         self.con.commit()
1062
1063     def save_if_not_exists(self, with_, direction, tim, msg='', nick=None):
1064         if tim:
1065             time_col = int(float(time.mktime(tim)))
1066         else:
1067             time_col = int(float(time.time()))
1068         if msg:
1069             if self.jid_is_from_pm(with_) or nick:
1070                 # It's a groupchat message
1071                 if nick:
1072                     # It's a message from a groupchat occupent
1073                     type_ = 'gc_msg'
1074                     with_ = with_ + '/' + nick
1075                 else:
1076                     # It's a server message message, we don't log them
1077                     return
1078             else:
1079                 if direction == 'from':
1080                     type_ = 'chat_msg_recv'
1081                 elif direction == 'to':
1082                     type_ = 'chat_msg_sent'
1083         jid_id = self.get_jid_id(with_)
1084         where_sql = 'jid_id = %s AND message=?' % jid_id
1085         if type_ == 'gc_msg':
1086             # We cannot differentiate gc message and pm messages, so look in
1087             # both logs
1088             with_2 = gajim.get_jid_without_resource(with_)
1089             if with_ != with_2:
1090                 jid_id2 = self.get_jid_id(with_2)
1091                 where_sql = 'jid_id in (%s, %s) AND message=?' % (jid_id,
1092                     jid_id2)
1093         start_time = time_col - 300 # 5 minutes arrount given time
1094         end_time = time_col + 300 # 5 minutes arrount given time
1095         self.cur.execute('''
1096             SELECT log_line_id FROM logs
1097             WHERE (%s)
1098             AND time BETWEEN %d AND %d
1099             ORDER BY time
1100             ''' % (where_sql, start_time, end_time), (msg,))
1101         results = self.cur.fetchall()
1102         if results:
1103             log.debug('Log already in DB, ignoring it')
1104             return
1105         log.debug('New log received from server archives, storing it')
1106         self.write(type_, with_, message=msg, tim=tim)
1107
1108     def _nec_gc_message_received(self, obj):
1109         tim_int = int(float(time.mktime(obj.timestamp)))
1110         if gajim.config.should_log(obj.conn.name, obj.jid) and not \
1111         tim_int <= obj.conn.last_history_time[obj.jid] and obj.msgtxt and \
1112         obj.nick:
1113             # if not obj.nick, it means message comes from room itself
1114             # usually it hold description and can be send at each connection
1115             # so don't store it in logs
1116             try:
1117                 self.write('gc_msg', obj.fjid, obj.msgtxt, tim=obj.timestamp)
1118                 # store in memory time of last message logged.
1119                 # this will also be saved in rooms_last_message_time table
1120                 # when we quit this muc
1121                 obj.conn.last_history_time[obj.jid] = time.mktime(obj.timestamp)
1122
1123             except exceptions.PysqliteOperationalError, e:
1124                 self.conn.dispatch('DB_ERROR', (_('Disk Write Error'), str(e)))
1125             except exceptions.DatabaseMalformed:
1126                 pritext = _('Database Error')
1127                 sectext = _('The database file (%s) cannot be read. Try to '
1128                     'repair it (see http://trac.gajim.org/wiki/DatabaseBackup) '
1129                     'or remove it (all history will be lost).') % \
1130                     LOG_DB_PATH
1131                 self.conn.dispatch('DB_ERROR', (pritext, sectext))