Keep a history of all the sessions, whatever their type, along with data associated with it:

  • All: SIP traces, basic info
  • Audio calls: recordings, answering machine messages
  • Chat: transcripts, MSRP trace
  • File transfers: path, MSRP trace
  • SMS: transcripts
  • Determine which backend to use for storing: sqlite/zodb/others. (preferably with support for full text search)
    • SQLite with fts3:
      • On Linux, fts3 seems to be precompiled (at least on Debian and Ubuntu)
      • fts3 is not precompiled in the sqlite3.dll shipped with the binary distribution of Python for Windows (2.5, 2.6 or 2.7); however, sqlite.org has a binary distribution for Windows consisting of a .dll and a .def which does have fts3 precompiled; since we will be shipping blink using py2exe, we should be able to easily replace the default dll with the one with fts3 support
      • This page describes the use of sqlite with fts3.
      • It may not be possible (or desirable due to performance penalties) to use an ORM: SQLObject does not seem to support fts3, while SQLAlchemy seems to partially support it; it may also be the case that an ORM would not be able to construct optimal queries as required by fts3.
    • ZODB with indexing:
      • It seems that indexing support is not distributed in Debian or Ubuntu older than lucid (10.04). The corresponding Ubuntu lucid packages are python-zope.catalog and python-zope.index (with quite a few dependencies)
      • Seems to use more than twise as much storage space than SQLite for the same data
      • Might need repacking of data at various intervals, operation which is cpu-intensive
    • Tests
      • The tests were performed using a SIP trace file generated by the command line clients having 416MB (595895 packets)
      • SQLite with fts3 (scripts used are attached):
        • Database size: 1000MB
        • Creation time: 2 minutes 51 seconds
        • Searching for (found 3028 packets) and retrieving results: 255 ms
        • Searching for 4a14e48dbc421d5b3521d1247fdb41871248e96f (a nonce, found 2 packets) and retrieving results: 6ms
      • ZODB with indexing: after 3 hours and 11GB written, the process stopped due to lack of free space on the filesystem. It should be noted that the process was only indexing the packet contents and not the other fields. A ZODB database without any index occupies approximately the same size as the SIP trace file used, thus the difference occurs exclusively due to the index.
  • Allow the user to perform complex searches in the history database
  • Needs to be integrated with logging

createdb.py - Script for creating the SQLite with FTS3 database from a SIP trace file (1.6 kB) Adrian Georgescu, 08/29/2010 01:33 pm

querydb.py - Script for querying a SQLite with FTS3 database (472 Bytes) Adrian Georgescu, 08/29/2010 01:33 pm

createzodb.py - Script for creating the ZODB with indexing database from a SIP trace file (1.7 kB) Adrian Georgescu, 08/29/2010 01:34 pm

packet.py - Module with the object definition for the data (577 Bytes) Adrian Georgescu, 08/29/2010 01:34 pm