From df60a1ba99b3830e9d5b0f2d90cd416f58937e89 Mon Sep 17 00:00:00 2001 From: Razvan Deaconescu Date: Tue, 16 Aug 2011 20:53:13 +0300 Subject: [PATCH] Restore DatabaseAccess.py. --- ppf/db/DatabaseAccess.py | 349 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 349 insertions(+) create mode 100644 ppf/db/DatabaseAccess.py diff --git a/ppf/db/DatabaseAccess.py b/ppf/db/DatabaseAccess.py new file mode 100644 index 0000000..527a02e --- /dev/null +++ b/ppf/db/DatabaseAccess.py @@ -0,0 +1,349 @@ +#!/usr/bin/env python + +import sys +import sqlite3 +import os.path + +DEBUG = False + +class DatabaseAccess: + """ + Low-level class for database access: insert, update, delete, + select operations + Basic operations on each table in P2P logging database: swarms, + btclients, client_sessions, status_messages, verbose_messages + Insert methods have dual options: + insert_swarms_row - inserts a row as an array + insert_swarms - row fields to be added are passed as separate + arguments + """ + operators={'eq':'=', 'neq':'<>', 'gt':'>', 'gte':'>=', 'lt':'<', 'lte':'<=', 'lk':'LIKE'} + + def __init__ (self, dbname): + self.dbname = dbname + + def connect(self): + if not os.path.isfile(self.dbname): + return False + self.conn = sqlite3.connect(self.dbname) + self.cursor = self.conn.cursor() + return True + + def disconnect(self): + self.cursor.close() + self.conn.close() + + def get_cursor(self): + return self.cursor + + def get_connection(self): + return self.conn + + def get_status(self): + """ + Select rows in all tables + """ + tables = ['swarms', 'btclients', 'client_sessions', 'status_messages', 'verbose_messages'] + for t in tables: + try: + self.cursor.execute("select * from '%s'" %t) + for row in self.cursor: + print row + except sqlite3.Error, e: + print "[select] error: ", e.args[0] + + def insert_swarms_row(self, row): + try: + self.cursor.execute("insert into swarms(torrent, filesize, purpose, source) values (?,?,?,?)", row) + self.conn.commit() + except sqlite3.Error, e: + print ("[swarms]An error ocurred: ", e.args[0]) + + def insert_swarms(self, torrent_file, filesize, purpose, source): + self.insert_swarms_row([torrent_file, filesize, purpose, source]) + + def select_swarms(self, show = True, swarm_id = -1): + try: + if swarm_id == -1: + self.cursor.execute("select * from swarms") + else: + self.cursor.execute("select * from swarms where id='%s'" %swarm_id) + + if show == True: + for row in self.cursor: + print row + else: + return self.cursor + except sqlite3.Error, e: + print("[swarms]An error ocurred: ", e.args[0]) + + def delete_swarms(self, swarm_id = -1): + try: + if swarm_id == -1: + self.cursor.execute("delete from swarms") + else: + self.cursor.execute("delete from swarms where id=?", (swarm_id,)) + self.conn.commit() + except sqlite3.Error, e: + print("[swarms]An error ocurred: ", e.args[0]) + + def insert_btclients_row(self, row): + try: + self.cursor.execute("insert into btclients(name, language, dht, streaming) values (?,?,?,?)", row) + self.conn.commit() + except sqlite3.Error, e: + print ("[btclients]An error ocurred: ", e.args[0]) + + def insert_btclients(self, client_name, language, dht, streaming): + btclients([client_name, language, dht, streaming]) + + def select_btclients(self, show = True, id = -1): + try: + if id == -1: + self.cursor.execute("""select * from btclients""") + else: + self.cursor.execute("""select * from btclients where id='%s'""" %id) + if show == True: + for row in self.cursor: + print row + else: + return self.cursor + except sqlite3.Error, e: + print ("[btclients]An error ocurred: ", e.args[0]) + + def select_btclient_by_name(self, client_name, show = True): + try: + self.cursor.execute("""select * from btclients where name='%s'""" %client_name) + if show == True: + for row in self.cursor: + print row + else: + return self.cursor + except sqlite3.Error, e: + print ("[btclients]An error ocurred: ", e.args[0]) + + def select_btclient_id_by_name(self, client_name): + try: + self.cursor.execute("""select * from btclients where name='%s'""" %client_name) + for row in self.cursor: + return row[0] + except sqlite3.Error, e: + print ("[btclients]An error ocurred: ", e.args[0]) + + def insert_client_sessions_row(self, row): + try: + self.cursor.execute("insert into client_sessions(swarm_id, client_id, system_os, system_os_version, system_ram, system_cpu, public_ip, public_port, ds_limit, us_limit, start_time) values (?,?,?,?,?,?,?,?,?,?,?)", row) + self.conn.commit() + except sqlite3.Error, e: + print ("[client_sessions]An error ocurred: ", e.args[0]) + + def insert_client_sessions(self, swarm_id, client_id, system_os, system_os_version, system_ram, system_cpu, public_ip, public_port, ds_limit, us_limit, start_time): + self.insert_client_sessions_row([swarm_id, client_id, system_os, system_os_version, system_ram, system_cpu, public_ip, public_port, ds_limit, us_limit, start_time]); + + def select_client_sessions_by_id(self, show = True, cs_id = -1): + try: + if cs_id == -1: + self.cursor.execute("""select * from client_sessions""") + else: + self.cursor.execute("""select * from client_sessions where id='%s'""" %cs_id) + if show == True: + for row in self.cursor: + print row + else: + return self.cursor + except sqlite3.Error, e: + print ("[client_sessions]An error ocurred: ", e.args[0]) + + def select_client_sessions_by_swarm(self, show = True, swarm_id = -1, client_id = None): + try: + if client_id == None: + if swarm_id == -1: + self.cursor.execute("""select * from client_sessions""") + else: + self.cursor.execute("""select * from client_sessions where swarm_id=?""", (swarm_id, )) + else: + if swarm_id == -1: + self.cursor.execute("""select * from client_sessions where client_id=?""", (client_id, )) + else: + self.cursor.execute("""select * from client_sessions where swarm_id=? and client_id=?""", (swarm_id, client_id)) + + if show == True: + for row in self.cursor: + print row + else: + return self.cursor + except sqlite3.Error, e: + print ("[client_sessions]An error ocurred: ", e.args[0]) + + def delete_client_sessions_by_id(self, cs_id = -1): + try: + if cs_id == -1: + self.cursor.execute("""delete from client_sessions""") + else: + self.cursor.execute("""delete from client_sessions where id=?""", (cs_id, )) + self.conn.commit() + except sqlite3.Error, e: + print ("[client_sessions]An error ocurred: ", e.args[0]) + + def delete_client_sessions_by_swarm(self, swarm_id = -1, client_id = None): + try: + if client_id == None: + if swarm_id == -1: + self.cursor.execute("""delete from client_sessions""") + else: + self.cursor.execute("""delete from client_sessions where swarm_id=?""", (swarm_id, )) + else: + if swarm_id == -1: + self.cursor.execute("""delete from client_sessions where client_id=?""", (client_id, )) + else: + self.cursor.execute("""delete from client_sessions where swarm_id=? and client_id=?""", (swarm_id, client_id)) + self.conn.commit() + except sqlite3.Error, e: + print ("[client_sessions]An error ocurred: ", e.args[0]) + + def insert_status_messages_row(self, row): + if DEBUG == True: + print "[status_messages] insert row", row + try: + self.cursor.execute("insert into status_messages values (?,?,?,?,?,?,?,?,?)", row) + self.conn.commit() + except sqlite3.Error, e: + print ("[status_messages]An error ocurred: ", e.args[0]) + + def insert_status_messages(self, cs_id, timestamp, peer_num, dht, download_speed, upload_speed, download_size, upload_size, eta): + self.insert_status_messages_row([cs_id, timestamp, peer_num, dht, download_speed, upload_speed, download_size, upload_size, eta]) + + def select_status_messages(self, show = True, cs_id = -1, restrictArray=None): + try: + if cs_id == -1: + self.cursor.execute("select * from status_messages") + else: + values = (cs_id, ) + query = "select * from status_messages where cs_id=? and " + + if restrictArray: + for (key, value, op) in restrictArray: + query += "%s %s ? and " % (key, self.operators[op]) + values += (value, ) + + query = query.strip('and ') + print query, values + self.cursor.execute(query, values) + + if show == True: + for row in self.cursor: + print row + else: + return self.cursor + except sqlite3.Error, e: + print("[status_messages]An error ocurred: ", e.args[0]) + + def delete_status_messages(self, cs_id = -1): + try: + if cs_id == -1: + self.cursor.execute("delete from status_messages") + else: + self.cursor.execute("delete from status_messages where cs_id=?", (cs_id, )) + self.conn.commit() + except sqlite3.Error, e: + print("[status_messages]An error ocurred: ", e.args[0]) + + def insert_verbose_messages_row(self, row): + if DEBUG == True: + print "[verbose_messages] insert row", row + try: + self.cursor.execute("insert into verbose_messages values (?,?,?,?,?,?,?,?,?,?)", row) + self.conn.commit() + except sqlite3.Error, e: + print ("[verbose_messages]An error ocurred: ", e.args[0]) + + def insert_verbose_messages(self, cs_id, timestamp, direction, peer_ip, peer_port, message_type, _index, begin, length, listen_port): + self.insert_verbose_messages_row([cs_id, timestamp, direction, peer_ip, peer_port, message_type, _index, begin, length, listen_port]) + + def select_verbose_messages(self, show = True, cs_id = -1, restrictArray=None): + try: + if cs_id == -1: + self.cursor.execute("select * from verbose_messages") + else: + values = (cs_id, ) + query = "select * from verbose_messages where cs_id=? and " + if restrictArray: + for (key, value, op) in restrictArray: + query += "%s %s ? and " % (key, self.operators[op]) + values += (value, ) + + query = query.strip('and ') + #print query, values + self.cursor.execute(query, values) + + if show == True: + for row in self.cursor: + print row + else: + return self.cursor + except sqlite3.Error, e: + print("[status_messages]An error ocurred: ", e.args[0]) + + def delete_verbose_messages(self, cs_id = -1): + try: + if cs_id == -1: + self.cursor.execute("delete from verbose_messages") + else: + self.cursor.execute("delete from verbose_messages where cs_id=?", (cs_id, )) + self.conn.commit() + except sqlite3.Error, e: + print("[status_messages]An error ocurred: ", e.args[0]) + + +def main(): + + """ + Test case + """ + + if len(sys.argv) != 2: + print "Usage: python DatabaseAccess dbfile" + sys.exit(2) + + dba = DatabaseAccess(sys.argv[1]) + + dba.connect() + + for t in [('DarkKnight', '123000', 'experiment', 'TVTorrents'), + ('Fedora', '1024', 'experiment', 'local'), + ('Pulp Fiction', '102400', 'streaming', 'isohunt'), + ('Karaoke', 'anaaremere', 'streaming', 'local'), + ]: + dba.insert_swarms_row(t) + + for t in [('Tribler', 'Python', '1', '1'), + ('libtorrent', 'C++', '1', '0'), + ('Vuze', 'Java', '1', '0'), + ('Transmission', 'C', 'asa', '0'), + ]: + dba.insert_btclients_row(t) + + for t in [('1', '2', 'Linux', '2.6.26', '512', '1500', '141.85.224.205', '50500', '512', '64', '2455125.02409722'), + ('3', '4', 'Linux', '2.6.26', '512', '1500', '141.85.224.209', '40400', '512', '64', '2455125.03174769'), + ]: + dba.insert_client_sessions_row(t) + + for t in [('1', '2455128.10', '222', '0', '213', '56', '200', '300', '121.324'), + ('6', '2455128.10', '222', '0', '213', '56', '200', '300', '121.324'), + ]: + dba.insert_status_messages_row(t) + + for t in [('1', '2455128.121295811', '0', '127.0.0.1', '1345', '0', '3', '4', '13', '777'), + ('4', '2455128.121295811', '1', '127.0.0.1', '1345', '0', '3', '4', '13', '777'), + ]: + dba.insert_verbose_messages_row(t) + + dba.get_status() + + dba.select_btclient_by_name("Tribler") + + dba.disconnect() + + +if __name__ == "__main__": + sys.exit(main()) -- 2.20.1