1 # Written by Ali Abbas
\r
2 # see LICENSE.txt for license information
\r
8 from BaseLib.__init__ import LIBRARYNAME
\r
9 from BaseLib.Core.CacheDB.sqlitecachedb import *
\r
10 from BaseLib.Core.CacheDB.SqliteCacheDBHandler import BasicDBHandler
\r
12 CREATE_FRIENDSHIP_STATS_SQL_FILE = None
\r
13 CREATE_FRIENDSHIP_STATS_SQL_FILE_POSTFIX = os.path.join(LIBRARYNAME, 'Core', 'Statistics', 'tribler_friendship_stats_sdb.sql')
\r
14 DB_FILE_NAME = 'tribler_friendship_stats.sdb'
\r
15 DB_DIR_NAME = 'sqlite' # db file path = DB_DIR_NAME/DB_FILE_NAME
\r
16 CURRENT_DB_VERSION = 2
\r
20 def init_friendship_stats(config, db_exception_handler = None):
\r
21 """ create friendship statistics database """
\r
22 global CREATE_FRIENDSHIP_STATS_SQL_FILE
\r
23 config_dir = config['state_dir']
\r
24 install_dir = config['install_dir']
\r
25 CREATE_FRIENDSHIP_STATS_SQL_FILE = os.path.join(install_dir,CREATE_FRIENDSHIP_STATS_SQL_FILE_POSTFIX)
\r
26 sqlitedb = SQLiteFriendshipStatsCacheDB.getInstance(db_exception_handler)
\r
27 sqlite_db_path = os.path.join(config_dir, DB_DIR_NAME, DB_FILE_NAME)
\r
28 sqlitedb.initDB(sqlite_db_path, CREATE_FRIENDSHIP_STATS_SQL_FILE,current_db_version=CURRENT_DB_VERSION) # the first place to create db in Tribler
\r
32 class FSCacheDBBaseV2(SQLiteCacheDBBase):
\r
33 """ See Tribler/Core/Statistics/tribler_friendship_stats_sdb.sql
\r
34 for a description of the various versions
\r
37 def updateDB(self,fromver,tover):
\r
39 print >>sys.stderr,"fscachedb2: Upgrading",fromver,tover
\r
40 if fromver == 1 and tover == 2:
\r
41 # Do ALTER TABLE stuff to add crawler_permid field.
\r
42 sql = "ALTER TABLE FriendshipStatistics ADD COLUMN crawled_permid TEXT DEFAULT client NOT NULL;"
\r
43 self.execute_write(sql, commit=False)
\r
44 # updating version stepwise so if this works, we store it
\r
45 # regardless of later, potentially failing updates
\r
46 self.writeDBVersion(2, commit=False)
\r
50 class SQLiteFriendshipStatsCacheDB(FSCacheDBBaseV2):
\r
51 __single = None # used for multithreaded singletons pattern
\r
52 lock = threading.RLock()
\r
55 def getInstance(cls, *args, **kw):
\r
56 # Singleton pattern with double-checking to ensure that it can only create one object
\r
57 if cls.__single is None:
\r
60 if cls.__single is None:
\r
61 cls.__single = cls(*args, **kw)
\r
66 def __init__(self, *args, **kw):
\r
67 # always use getInstance() to create this object
\r
68 if self.__single != None:
\r
69 raise RuntimeError, "SQLiteFriendshipStatsCacheDB is singleton"
\r
71 FSCacheDBBaseV2.__init__(self, *args, **kw)
\r
75 class FriendshipStatisticsDBHandler(BasicDBHandler):
\r
77 __single = None # used for multi-threaded singletons pattern
\r
78 lock = threading.Lock()
\r
80 def getInstance(*args, **kw):
\r
81 # Singleton pattern with double-checking
\r
82 if FriendshipStatisticsDBHandler.__single is None:
\r
83 FriendshipStatisticsDBHandler.lock.acquire()
\r
85 if FriendshipStatisticsDBHandler.__single is None:
\r
86 FriendshipStatisticsDBHandler(*args, **kw)
\r
88 FriendshipStatisticsDBHandler.lock.release()
\r
89 return FriendshipStatisticsDBHandler.__single
\r
91 getInstance = staticmethod(getInstance)
\r
94 if FriendshipStatisticsDBHandler.__single is not None:
\r
95 raise RuntimeError, "FriendshipStatisticsDBHandler is singleton"
\r
96 FriendshipStatisticsDBHandler.__single = self
\r
97 db = SQLiteFriendshipStatsCacheDB.getInstance()
\r
98 BasicDBHandler.__init__(self, db, 'FriendshipStatistics')
\r
99 #BasicDBHandler.__init__(self, 'Peer')
\r
100 #self.tableName = 'FriendshipStatistics'
\r
103 def getAllFriendshipStatistics(self, permid, last_update_time = None, range = None, sort = None, reverse = False):
\r
106 db keys: 'source_permid', 'target_permid', 'isForwarder', 'request_time', 'response_time',
\r
107 'no_of_attempts', 'no_of_helpers'
\r
109 @in: get_online: boolean: if true, give peers a key 'online' if there is a connection now
\r
112 value_name = ('source_permid', 'target_permid', 'isForwarder', 'request_time', 'response_time', 'no_of_attempts',
\r
113 'no_of_helpers', 'modified_on')
\r
114 where = 'request_time > '+str(last_update_time) # source_permid done below
\r
118 limit = range[1] - range[0]
\r
120 limit = offset = None
\r
122 desc = (not reverse) and 'desc' or ''
\r
123 if sort in ('name'):
\r
124 order_by = ' lower(%s) %s' % (sort, desc)
\r
126 order_by = ' %s %s' % (sort, desc)
\r
130 permidstr = bin2str(permid)
\r
131 res_list = self.getAll(value_name, where=where, offset= offset, limit=limit, order_by=order_by, source_permid=permidstr)
\r
134 print >>sys.stderr,"FriendshipStatisticsDBHandler: getAll: result is",res_list
\r
138 def saveFriendshipStatisticData (self, data):
\r
140 self._db.insertMany('FriendshipStatistics', data)
\r
142 def insertFriendshipStatistics(self, my_permid, target_permid, current_time, isForwarder = 0, no_of_attempts = 0, no_of_helpers = 0, commit = True):
\r
144 # db keys: 'source_permid', 'target_permid', 'isForwarder', 'request_time', 'response_time',
\r
145 # 'no_of_attempts', 'no_of_helpers'
\r
146 # self._db.insert(self.table_name, entry=key, value=value)
\r
148 sql_insert_friendstatistics = "INSERT INTO FriendshipStatistics (source_permid, target_permid, isForwarder, request_time, response_time, no_of_attempts, no_of_helpers, modified_on) VALUES ('"+my_permid+"','"+target_permid+"',"+str(isForwarder)+","+str(current_time)+", 0 , "+str(no_of_attempts)+","+str(no_of_helpers)+","+str(current_time)+")"
\r
150 self._db.execute_write(sql_insert_friendstatistics,commit=commit)
\r
152 def updateFriendshipStatistics(self, my_permid, target_permid, current_time, isForwarder = 0, no_of_attempts = 0, no_of_helpers = 0, commit = True):
\r
154 sql_insert_friendstatistics = "UPDATE FriendshipStatistics SET request_time = "+str(current_time) +", no_of_attempts = "+str(no_of_attempts)+", no_of_helpers = "+str(no_of_helpers)+", modified_on = "+str(current_time)+" where source_permid = '"+my_permid+"' and target_permid = '"+target_permid+"'"
\r
156 self._db.execute_write(sql_insert_friendstatistics,commit=commit)
\r
158 def updateFriendshipResponseTime(self, my_permid, target_permid, current_time, commit = True):
\r
161 sql_insert_friendstatistics = "UPDATE FriendshipStatistics SET response_time = "+str(current_time)+ ", modified_on = "+str(current_time)+" where source_permid = '"+my_permid+"' and target_permid = '"+target_permid+"'"
\r
164 print >> sys.stderr, sql_insert_friendstatistics
\r
166 self._db.execute_write(sql_insert_friendstatistics,commit=commit)
\r
168 def insertOrUpdateFriendshipStatistics(self, my_permid, target_permid, current_time, isForwarder = 0, no_of_attempts = 0, no_of_helpers = 0, commit = True):
\r
170 # sql_entry_exists_of_the_peer = "SELECT souce_permid FROM FriendshipStatistics where source_permid = " + my_permid
\r
172 print >> sys.stderr, 'Friendship record being inserted of permid'
\r
173 print >> sys.stderr, target_permid
\r
174 res = self._db.getOne('FriendshipStatistics', 'target_permid', target_permid=target_permid)
\r
177 sql_insert_friendstatistics = "INSERT INTO FriendshipStatistics (source_permid, target_permid, isForwarder, request_time, response_time, no_of_attempts, no_of_helpers, modified_on) VALUES ('"+my_permid+"','"+target_permid+"',"+str(isForwarder)+","+str(current_time)+", 0 , "+str(no_of_attempts)+","+str(no_of_helpers)+","+str(current_time)+")"
\r
179 sql_insert_friendstatistics = "UPDATE FriendshipStatistics SET no_of_attempts = "+str(no_of_attempts)+", no_of_helpers = "+str(no_of_helpers)+", modified_on = "+str(current_time)+" where source_permid = '"+my_permid+"' and target_permid = '"+target_permid+"'"
\r
182 print >> sys.stderr, 'result is ', res
\r
183 print >> sys.stderr, sql_insert_friendstatistics
\r
186 self._db.execute_write(sql_insert_friendstatistics,commit=commit)
\r
188 print >> sys.stderr
\r
191 def getLastUpdateTimeOfThePeer(self, permid):
\r
193 res = self._db.getAll('FriendshipStatistics', 'source_permid', order_by='modified_on desc', limit=1)
\r
199 return 0 # bug??? res['modified_on']
\r