From 7e537f740a03e39117003fb128a814b41610b849 Mon Sep 17 00:00:00 2001 From: Mariana Marasoiu Date: Fri, 10 Jun 2011 16:23:25 +0300 Subject: [PATCH] added db-mysql/ and log-parser-mysql/ folders and mysql schema file --- ppf/db-mysql/.gitignore | 2 + ppf/db-mysql/DatabaseAccess.py | 348 ++++ ppf/db-mysql/DatabaseCommander.py | 325 ++++ ppf/db-mysql/DatabaseWriter.py | 292 ++++ ppf/db-mysql/README | 213 +++ ppf/db-mysql/client_sessions.sample.txt | 40 + ppf/db-mysql/db_init | 14 + ppf/db-mysql/julian.py | 52 + ppf/db-mysql/sidereal.py | 1457 +++++++++++++++++ ppf/db-mysql/status_messages.sample.txt | 8 + ppf/db-mysql/swarms.sample.txt | 1 + ppf/db-mysql/verbose_messages.sample.txt | 8 + .../generic/GenericStatusParser.py | 137 ++ .../generic/LibtorrentStatusParser.py | 145 ++ .../generic/TriblerStatusParser.py | 166 ++ .../generic/libtorrent_parser_test | 6 + .../generic/tribler_parser_test | 5 + ppf/log-parser-mysql/libtorrent/LogParser.py | 570 +++++++ ppf/log-parser-mysql/libtorrent/README | 30 + .../libtorrent/StatusParser.py | 224 +++ ppf/log-parser-mysql/libtorrent/log_parser | 54 + .../libtorrent/parse_log_file | 17 + ppf/log-parser-mysql/libtorrent/run_sample | 20 + ppf/log-parser-mysql/tribler/LogParser.py | 423 +++++ ppf/log-parser-mysql/tribler/StatusParser.py | 269 +++ ppf/log-parser-mysql/tribler/make_db | 15 + .../tribler/merge_status_msg.sh | 10 + ppf/log-parser-mysql/tribler/run_sample | 8 + .../tribler/run_sample_verbose | 9 + ppf/sql/p2p-log-mysql.sql | 78 + 30 files changed, 4946 insertions(+) create mode 100644 ppf/db-mysql/.gitignore create mode 100644 ppf/db-mysql/DatabaseAccess.py create mode 100644 ppf/db-mysql/DatabaseCommander.py create mode 100644 ppf/db-mysql/DatabaseWriter.py create mode 100644 ppf/db-mysql/README create mode 100644 ppf/db-mysql/client_sessions.sample.txt create mode 100755 ppf/db-mysql/db_init create mode 100644 ppf/db-mysql/julian.py create mode 100644 ppf/db-mysql/sidereal.py create mode 100644 ppf/db-mysql/status_messages.sample.txt create mode 100644 ppf/db-mysql/swarms.sample.txt create mode 100644 ppf/db-mysql/verbose_messages.sample.txt create mode 100644 ppf/log-parser-mysql/generic/GenericStatusParser.py create mode 100644 ppf/log-parser-mysql/generic/LibtorrentStatusParser.py create mode 100644 ppf/log-parser-mysql/generic/TriblerStatusParser.py create mode 100755 ppf/log-parser-mysql/generic/libtorrent_parser_test create mode 100755 ppf/log-parser-mysql/generic/tribler_parser_test create mode 100644 ppf/log-parser-mysql/libtorrent/LogParser.py create mode 100644 ppf/log-parser-mysql/libtorrent/README create mode 100644 ppf/log-parser-mysql/libtorrent/StatusParser.py create mode 100755 ppf/log-parser-mysql/libtorrent/log_parser create mode 100755 ppf/log-parser-mysql/libtorrent/parse_log_file create mode 100755 ppf/log-parser-mysql/libtorrent/run_sample create mode 100644 ppf/log-parser-mysql/tribler/LogParser.py create mode 100644 ppf/log-parser-mysql/tribler/StatusParser.py create mode 100755 ppf/log-parser-mysql/tribler/make_db create mode 100755 ppf/log-parser-mysql/tribler/merge_status_msg.sh create mode 100755 ppf/log-parser-mysql/tribler/run_sample create mode 100755 ppf/log-parser-mysql/tribler/run_sample_verbose create mode 100644 ppf/sql/p2p-log-mysql.sql diff --git a/ppf/db-mysql/.gitignore b/ppf/db-mysql/.gitignore new file mode 100644 index 0000000..231486c --- /dev/null +++ b/ppf/db-mysql/.gitignore @@ -0,0 +1,2 @@ +*.db +*.pyc diff --git a/ppf/db-mysql/DatabaseAccess.py b/ppf/db-mysql/DatabaseAccess.py new file mode 100644 index 0000000..6b31b9b --- /dev/null +++ b/ppf/db-mysql/DatabaseAccess.py @@ -0,0 +1,348 @@ +#!/usr/bin/env python + +import sys +import MySQLdb +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 = MySQLdb.Connection(db = self.dbname, + user = "root", + passwd = "p2p4th3m42232") + 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 MySQLdb.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 (%s,%s,%s,%s)", row) + self.conn.commit() + except MySQLdb.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 MySQLdb.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=%s", (swarm_id,)) + self.conn.commit() + except MySQLdb.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 (%s,%s,%s,%s)", row) + self.conn.commit() + except MySQLdb.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 MySQLdb.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 MySQLdb.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 MySQLdb.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 (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", row) + self.conn.commit() + except MySQLdb.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 MySQLdb.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=%s""", (swarm_id, )) + else: + if swarm_id == -1: + self.cursor.execute("""select * from client_sessions where client_id=%s""", (client_id, )) + else: + self.cursor.execute("""select * from client_sessions where swarm_id=%s and client_id=%s""", (swarm_id, client_id)) + + if show == True: + for row in self.cursor: + print row + else: + return self.cursor + except MySQLdb.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=%s""", (cs_id, )) + self.conn.commit() + except MySQLdb.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=%s""", (swarm_id, )) + else: + if swarm_id == -1: + self.cursor.execute("""delete from client_sessions where client_id=%s""", (client_id, )) + else: + self.cursor.execute("""delete from client_sessions where swarm_id=%s and client_id=%s""", (swarm_id, client_id)) + self.conn.commit() + except MySQLdb.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 (%s,%s,%s,%s,%s,%s,%s,%s,%s)", row) + self.conn.commit() + except MySQLdb.Error, e: + print ("[status_messages1]An error ocurred: ", e) + + 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=%s 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 MySQLdb.Error, e: + print("[status_messages]An error ocurred: ", e.args[1]) + + 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=%s", (cs_id, )) + self.conn.commit() + except MySQLdb.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 (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", row) + self.conn.commit() + except MySQLdb.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=%s 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 MySQLdb.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=%s", (cs_id, )) + self.conn.commit() + except MySQLdb.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'), + ]: + dba.insert_swarms_row(t) + + for t in [('Tribler', 'Python', '1', '1'), + ('libtorrent', 'C++', '1', '0'), + ('Vuze', 'Java', '1', '0'), + ]: + dba.insert_btclients_row(t) + + for t in [('1', '2', 'Linux', '2.6.26', '512', '1500', '141.85.224.205', '50500', '512', '64', '2011-05-17 13:23:54'), + ('3', '4', 'Linux', '2.6.26', '512', '1500', '141.85.224.209', '40400', '512', '64', '2011-05-18 13:22:12'), + ]: + dba.insert_client_sessions_row(t) + + for t in [('1', '2011-05-17 13:12:45', '222', '0', '213', '56', '200', '300', '2m15s'), + ('6', '2011-05-14 13:20:12', '222', '0', '213', '56', '200', '300', '3m43s'), + ]: + dba.insert_status_messages_row(t) + + for t in [('1', '2011-05-17 13:13:21', '0', '127.0.0.1', '1345', '0', '3', '4', '13', '777'), + ('4', '2011-05-18 14:36:41', '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()) diff --git a/ppf/db-mysql/DatabaseCommander.py b/ppf/db-mysql/DatabaseCommander.py new file mode 100644 index 0000000..126633d --- /dev/null +++ b/ppf/db-mysql/DatabaseCommander.py @@ -0,0 +1,325 @@ +#!/usr/bin/env python + +import sys +import getopt +import MySQLdb +#import julian +#import datetime +from DatabaseAccess import DatabaseAccess + +DEBUG = False + +class DatabaseCommander: + """ + swarms and client_sessions table handling methods + Wrappers around DatabaseAccess class methods + """ + + def __init__ (self, dbname): + self.dbname = dbname + self.dba = DatabaseAccess(dbname) + self.dba.connect() + + def add_swarm(self, torrent_file, filesize, purpose, source): + self.dba.insert_swarms(torrent_file, filesize, purpose, source); + + def add_client_session(self, swarm_id, client_name, system_os, + system_os_version, system_ram, system_cpu, public_ip, + public_port, ds_limit, us_limit, timestamp): +# jd = float(julian.stringToJulian(date, time)) + client_id = self.dba.select_btclient_id_by_name(client_name) + self.dba.insert_client_sessions(swarm_id, client_id, + system_os, system_os_version, system_ram, system_cpu, + public_ip, public_port, ds_limit, us_limit, timestamp) + + def show_swarms(self, swarm_id = -1): + self.dba.select_swarms(True, swarm_id) + + def select_swarms(self, swarm_id = -1): + return self.dba.select_swarms(False, swarm_id) + + def show_btclients(self, client_id = -1): + self.dba.select_btclients(True, client_id) + + def select_btclients(self, client_id = -1): + return self.dba.select_btclients(False, client_id) + + def show_client_sessions_by_id(self, client_id = -1): + self.dba.select_client_sessions_by_id(True, client_id); + + def select_client_sessions_by_id(self, client_id = -1): + return self.dba.select_client_sessions_by_id(False, client_id); + + def show_client_sessions_by_swarm(self, swarm_id, client_name = None): + client_id = None + if client_name != None: + client_id = self.dba.select_btclient_id_by_name(client_name) + self.dba.select_client_sessions_by_swarm(True, swarm_id, client_id) + + def select_client_sessions_by_swarm(self, swarm_id, client_name = None): + client_id = None + if client_name != None: + client_id = self.dba.select_btclient_id_by_name(client_name) + self.dba.select_client_sessions_by_swarm(False, swarm_id, client_id) + + def delete_swarm(self, swarm_id = -1): + self.dba.delete_swarms(swarm_id) + + def delete_client_session_by_id(self, client_session_id = -1): + self.dba.delete_client_sessions_by_id(client_session_id) + + def delete_client_sessions_by_swarm(self, swarm_id, client_name = None): + client_id = None + if client_name != None: + client_id = self.dba.select_btclient_id_by_name(client_name) + self.dba.delete_client_sessions_by_swarm(swarm_id, client_id) + + +def usage(): + print "Usage: python DatabaseCommander.py action target [-i|--id id] [options] database" + print "action:" + print "\t--add" + print "\t-a\t\tadd entry to database" + print "\t\t\t\t(information is read from standard input)" + print "\t--list" + print "\t-l\t\tlist entry/entries from database" + print "\t--delete" + print "\t-d\t\tdelete entry from database" + print "\t--read" + print "\t-r\t\tread information from standard input" + print "target:" + print "\tswarm\t\tswarm entries" + print "\tsession\t\tclient sessions" + print "id:" + print "\t--id" + print "\t-i\t\tswarm_id or client_session_id" + print "options:" + print "\t--sep" + print "\t-m\t\tuse separator string instead of standard comma (,)" + print "\t--swarm id" + print "\t-s id\t\tspecify swarm_id" + print "\t--client name" + print "\t-c\t\tspecify client_name" + print "\tclient sessions may be listed by specifying swarm_id and, optionally, client name" + print "\tdatabase\t\tSQLite database file" + print "\t--help" + print "\t-h\t\t\tprint this help screen" + + +def read_swarms(dbc, sep = None): + if sep == None: + sep = ',' + + while 1: + line = sys.stdin.readline().strip() + if line: + message_array = line.split(sep) + + swarm_name = message_array[0].strip() + filesize = int(message_array[1].strip()) + purpose = message_array[2].strip() + source = message_array[3].strip() + + dbc.add_swarm(swarm_name, filesize, purpose, source) + else: + break + +def read_client_sessions(dbc, sep = None): + if sep == None: + sep = ',' + + while 1: + line = sys.stdin.readline().strip() + if line: + message_array = line.split(sep) + + swarm_id = int(message_array[0].strip()) + client_name = message_array[1].strip() + system_os = message_array[2].strip() + system_os_version = message_array[3].strip() + system_ram = int(message_array[4].strip()) + system_cpu = int(message_array[5].strip()) + public_ip = message_array[6].strip() + public_port = int(message_array[7].strip()) + ds_limit = int(message_array[8].strip()) + us_limit = int(message_array[9].strip()) + timestamp = message_array[10].strip()#.split(' ') +# date = timestamp[0] +# time = timestamp[1] + + if DEBUG == True: + print "(%d, %s, %s, %s, %d, %d, %s, %d, %d, %d, %s)" %(swarm_id, client_name, system_os, system_os_version, system_ram, system_cpu, public_ip, public_port, ds_limit, us_limit, timestamp) + + dbc.add_client_session(swarm_id, client_name, system_os, + system_os_version, system_ram, system_cpu, public_ip, public_port, ds_limit, us_limit, timestamp) + else: + break + + +def main(): + """ + Command line interface for database handling. Allows insertion, + deletion and selection of rows in swarms and client_sessions tables. + If id == -1, all rows in target table are deleted/selected. + Uses getopt for command line parsing. + Last argument must be a database file. + Please check README for details and running examples. + """ + + try: + opts, args = getopt.getopt(sys.argv[1:], "h:a:l:d:r:m:s:c:i:", ["help", + "add=", "list=", "delete=", "read=", "sep=", "swarm=", "client=", "id="]) + except getopt.GetoptError, err: + print str(err) + usage() + sys.exit(2) + + action = None + target = None + id = None + swarm = None + client = None + client_session_list_option = None + database = None + sep = None + + for o, a in opts: + if o in ("-h", "--help"): + usage() + sys.exit(0) + elif o in ("-a", "--add"): + action = "add" + target = a + elif o in ("-d", "--delete"): + action = "delete" + target = a + elif o in ("-l", "--list"): + action = "list" + target = a + elif o in ("-r", "--read"): + action = "read" + target = a + elif o in ("-i", "--id"): + try: + id = int(a) + except TypeError, err: + print str(err) + sys.exit(2) + elif o in ("-s", "--swarm"): + try: + swarm = int(a) + except TypeError, err: + print str(err) + sys.exit(2) + elif o in ("-c", "--client"): + client = a + elif o in ("-m", "--sep"): + sep = a + else: + assert False, "unhandled option" + + # no database file passed as argument + if len(args) != 1: + print "Error: no database file passed as argument." + sys.exit(2) + database = args[0] + + if action == None: + print "Error: no action specified." + sys.exit(2) + + if target != "swarm" and target != "session": + print "Error: invalid target ", target, "." + sys.exit(2) + + if id != None and (action == "add" or action == "read"): + print "Error: %s action doesn't use an id field" % action + sys.exit(2) + + if target == "swarm" and id == None and (action == "list" or action == "delete"): + print "Error: no swarm_id specified for 'swarm' target and action %s." % action + sys.exit(2) + + if target == "session" and (action == "list" or action == "delete"): + if id == None: + if swarm == None: + print "Error: no swarm_id or client_session_id specified for 'session' target." + sys.exit(2) + client_session_list_option = "swarm" + else: + if swarm != None or client != None: + print "Error: simulataneous definition of client_session_id and swarm_id/client_name." + sys.exit(2) + client_session_list_option = "id" + + if action == "delete": + if swarm != None or client != None: + print "Error: too many arguments for delete action." + + if action == "add": + if swarm != None or client != None: + print "Error: too many arguments for delete action." + + dbc = DatabaseCommander(database) + + if target == "swarm": + if action == "add": + print "swarm name (torrent filename without .torrent extension): ", + swarm_name = sys.stdin.readline().strip() + print "file size (in bytes): ", + file_size = sys.stdin.readline().strip() + print "purpose (what is this warm used for): ", + purpose = sys.stdin.readline().strip() + print "source (URL for downloading .torrent file or \"local\"): ", + source = sys.stdin.readline().strip() + + dbc.add_swarm(swarm_name, file_size, purpose, source) + if action == "delete": + dbc.delete_swarm(id) + if action == "list": + dbc.show_swarms(id) + if action == "read": + read_swarms(dbc, sep) + + if target == "session": + if action == "add": + print "swarm id (swarm identifier in database): ", + swarm_id = sys.stdin.readline().strip() + print "client name (Tribler, libtorrent, etc.): ", + client_name = sys.stdin.readline().strip() + print "system OS (Linux, Windows, Mac OS X): ", + system_os = sys.stdin.readline().strip() + print "OS version (2.6.28, 7, 10.6): ", + os_version = sys.stdin.readline().strip() + print "system RAM (in MB): ", + ram = sys.stdin.readline().strip() + print "system CPU (in MHz): ", + cpu = sys.stdin.readline().strip() + print "public IP (dotted decimal format): ", + ip = sys.stdin.readline().strip() + print "public port: ", + port = sys.stdin.readline().strip() + print "download speed limit (in KB/s): ", + ds_limit = sys.stdin.readline().strip() + print "upload speed limit (in KB/s): ", + us_limit = sys.stdin.readline().strip() + print "start time (YYYY-MM-DD HH:MM:SS): ", + start_time = sys.stdin.readline().strip() + + dbc.add_client_session(swarm_id, client_name, system_os, + os_version, ram, cpu, ip, port, ds_limit, us_limit, start_time) + if action == "delete": + if client_session_list_option == "id": + dbc.delete_client_session_by_id(id) + elif client_session_list_option == "swarm": + dbc.delete_client_sessions_by_swarm(swarm, client) + if action == "list": + if client_session_list_option == "id": + dbc.show_client_sessions_by_id(id) + elif client_session_list_option == "swarm": + dbc.show_client_sessions_by_swarm(swarm, client) + if action == "read": + read_client_sessions(dbc, sep) + +if __name__ == "__main__": + sys.exit(main()) diff --git a/ppf/db-mysql/DatabaseWriter.py b/ppf/db-mysql/DatabaseWriter.py new file mode 100644 index 0000000..31b8af5 --- /dev/null +++ b/ppf/db-mysql/DatabaseWriter.py @@ -0,0 +1,292 @@ +#!/usr/bin/env python + +import sys +import getopt +#import julian +import datetime +import re # regular expression support +from DatabaseAccess import DatabaseAccess + +class DatabaseWriter: + def __init__ (self, dbname): + self.dbname = dbname + self.dba = DatabaseAccess(dbname) + self.dba.connect() + + def add_status_message(self, cs_id, timestamp, peer_num, dht, download_speed, upload_speed, download_size, upload_size, eta_seconds): +# timestamp = float(julian.stringToJulian(date, time)); + self.dba.insert_status_messages(cs_id, timestamp, peer_num, dht, download_speed, upload_speed, download_size, upload_size, eta_seconds) + + def add_status_message_datetime(self, cs_id, timestamp, peer_num, dht, download_speed, upload_speed, download_size, upload_size, eta_seconds): +# timestamp = float(julian.datetimeToJulian(dt)); + self.dba.insert_status_messages(cs_id, timestamp, peer_num, dht, download_speed, upload_speed, download_size, upload_size, eta_seconds) + + def add_verbose_message(self, cs_id, timestamp, direction, peer_ip, peer_port, message_type, index, begin, length, listen_port): +# timestamp = float(julian.stringToJulian(date, time)); + self.dba.insert_verbose_messages(cs_id, timestamp, direction, peer_ip, peer_port, message_type, index, begin, length, listen_port) + + def add_verbose_message_datetime(self, cs_id, timestamp, direction, peer_ip, peer_port, message_type, index, begin, length, listen_port): +# timestamp = float(julian.datetimeToJulian(dt)); + + self.dba.insert_verbose_messages(cs_id, timestamp, direction, peer_ip, peer_port, message_type, index, begin, length, listen_port) + + def show_status_messages(self, cs_id = -1): + self.dba.select_status_messages(True, cs_id) + + def select_status_messages(self, cs_id = -1): + return self.dba.select_status_messages(False, cs_id) + + def show_verbose_messages(self, cs_id = -1): + self.dba.select_verbose_messages(True, cs_id) + + def select_verbose_messages(self, cs_id = -1): + return self.dba.select_verbose_messages(False, cs_id) + + def delete_status_messages(self, cs_id = -1): + self.dba.delete_status_messages(cs_id) + + def delete_verbose_messages(self, cs_id = -1): + self.dba.delete_verbose_messages(cs_id) + + +def usage(): + print "Usage: python DatabaseWriter.py action target [-i|--id id] database" + print "action:" + print "\t--add" + print "\t-a\t\tadd entry to database" + print "\t\t\t\t(information is read from standard input)" + print "\t--list" + print "\t-l\t\tlist entry/entries from database" + print "\t--delete" + print "\t-d\t\tdelete entry from database" + print "\t--read" + print "\t-r\t\tread information from standard input" + print "target:" + print "\tstatus\t\tstatus messages" + print "\tverbose\t\tverbose messages" + print "id:" + print "\t--id" + print "\t-i\t\tclient_session_id" + print "\tdatabase\t\tSQLite database file" + print "\t--help" + print "\t-h\t\t\tprint this help screen" + +def read_status_messages(dbw, sep = None): + if sep == None: + sep = ',' + + while 1: + line = sys.stdin.readline().strip() + if line: + message_array = line.split(sep) + + cs_id = int(message_array[0].strip()) + timestamp = message_array[1].strip() +# date = timestamp[0] +# time = timestamp[1] + num_peers = int(message_array[2].strip()) + dht = int(message_array[3].strip()) + download_speed = int(message_array[4].strip()) + upload_speed = int(message_array[5].strip()) + download_size = int(message_array[6].strip()) + upload_size = int(message_array[7].strip()) + eta_string_array = re.split('[dhms]', message_array[8].strip()) + eta_string_array.remove('') + eta_array = [] + for i in range(0, len(eta_string_array)): + eta_array.append(int(eta_string_array[i])) + for i in range(len(eta_string_array), 4): + eta_array.insert(0, 0) + eta = ((eta_array[0]*24 + eta_array[1])*60 + eta_array[2])*60 + eta_array[3] + dbw.add_status_message(cs_id, timestamp, num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta) + else: + break + +def read_verbose_messages(dbw, sep = None): + if sep == None: + sep = ',' + + while 1: + line = sys.stdin.readline().strip() + if line: + message_array = line.split(sep) + + cs_id = int(message_array[0].strip()) + timestamp = message_array[1].strip() +# date = timestamp[0] +# time = timestamp[1] + direction = int(message_array[2].strip()) + peer_ip = message_array[3].strip() + peer_port = int(message_array[4].strip()) + message_type = int(message_array[5].strip()) + index = int(message_array[6].strip()) + begin = int(message_array[7].strip()) + length = int(message_array[8].strip()) + listen_port = int(message_array[9].strip()) + + dbw.add_verbose_message(cs_id, timestamp, direction, peer_ip, peer_port, + message_type, index, begin, length, listen_port) + else: + break + +def main(): + """ + Command line interface for database handling. Allows insertion, + deletion and selection of rows in status_messages and verbose_messages + tables. + If id == -1, all rows in target table are deleted/selected. + Uses getopt for command line parsing. + Last argument must be a database file. + Please check README for details and running examples. + """ + + try: + opts, args = getopt.getopt(sys.argv[1:], "ha:l:d:r:i:s:", ["help", + "add=", "list=", "delete=", "read=", "id=", "separator="]) + except getopt.GetoptError, err: + print str(err) + usage() + sys.exit(2) + + action = None + target = None + id = None + sep = None + + for o, a in opts: + if o in ("-h", "--help"): + usage() + sys.exit(0) + elif o in ("-a", "--add"): + action = "add" + target = a + elif o in ("-d", "--delete"): + action = "delete" + target = a + elif o in ("-l", "--list"): + action = "list" + target = a + elif o in ("-r", "--read"): + action = "read" + target = a + elif o in ("-i", "--id"): + try: + id = int(a) + except TypeError, err: + print str(err) + sys.exit(2) + elif o in ("-s", "--sep"): + sep = a + else: + assert False, "unhandled option" + + # no database file passed as argument + if len(args) != 1: + print "Error: no database file passed as argument." + sys.exit(2) + database = args[0] + + if action == None: + print "Error: no action specified." + sys.exit(2) + + if target != "status" and target != "verbose": + print "Error: invalid target", target, "." + sys.exit(2) + + if id != None and (action == "add" or action == "read"): + print "Error:", action, "action doesn't use an id field." + sys.exit(2) + + if id == None and (action == "delete" or action == "list"): + print "Error: no id for", action, "action." + sys.exit(2) + + if sep != None and action != "read": + print "Error:", action, "action doesn't use a separator argument." + sys.exit(2) + + dbw = DatabaseWriter(database) + + + if target == "status": + if action == "add": + print "client session id: ", + cs_id = int(sys.stdin.readline().strip()) + print "timestamp (YYYY-MM-DD HH:MM:SS.ss): ", + timestamp = sys.stdin.readline().strip() +# date = timestamp[0] +# time = timestamp[1] + print "number of peers: ", + num_peers = int(sys.stdin.readline().strip()) + print "dht: ", + dht = int(sys.stdin.readline().strip()) + print "current download speed (KB/s): ", + download_speed = int(sys.stdin.readline().strip()) + print "current upload speed (KB/s): ", + upload_speed = int(sys.stdin.readline().strip()) + print "download size (bytes): ", + download_size = int(sys.stdin.readline().strip()) + print "upload size (bytes): ", + upload_size = int(sys.stdin.readline().strip()) + print "eta (XdYhZmWs; e.g. 1d12h34m12s): ", + eta_string_array = re.split('[dhms]', sys.stdin.readline().strip()) + eta_string_array.remove('') + eta_array = [] + for i in range(0, len(eta_string_array)): + eta_array.append(int(eta_string_array[i])) + for i in range(len(eta_string_array), 4): + eta_array.insert(0, 0) + eta = ((eta_array[0]*24 + eta_array[1])*60 + eta_array[2])*60 + eta_array[3] + print eta + dbw.add_status_message(cs_id, timestamp, num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta) + + if action == "delete": + dbw.delete_status_messages(id) + + if action == "list": + dbw.show_status_messages(id) + + if action == "read": + read_status_messages(dbw, sep) + + if target == "verbose": + if action == "add": + print "client session id: ", + cs_id = int(sys.stdin.readline().strip()) + print "timestamp (YYYY-MM-DD HH:MM:SS.ss): ", + timestamp = sys.stdin.readline().strip() +# date = timestamp[0] +# time = timestamp[1] + print "direction (receive=0, send=1): ", + direction = int(sys.stdin.readline().strip()) + print "peer IP address (X.Y.Z.T): ", + peer_ip = sys.stdin.readline().strip() + print "peer port: ", + peer_port = int(sys.stdin.readline().strip()) + print "message_type (0, 1, 2, 3, 4, 5, 6): ", + message_type = int(sys.stdin.readline().strip()) + print "index: ", + index = int(sys.stdin.readline().strip()) + print "begin: ", + begin = int(sys.stdin.readline().strip()) + print "length: ", + length = int(sys.stdin.readline().strip()) + print "listen_port: ", + listen_port = int(sys.stdin.readline().strip()) + + dbw.add_verbose_message(cs_id, timestamp, direction, peer_ip, peer_port, + message_type, index, begin, length, listen_port) + + if action == "delete": + dbw.delete_verbose_messages(id) + + if action == "list": + dbw.show_verbose_messages(id) + + if action == "read": + read_verbose_messages(dbw, sep) + + +if __name__ == "__main__": + sys.exit(main()) diff --git a/ppf/db-mysql/README b/ppf/db-mysql/README new file mode 100644 index 0000000..e4ede0d --- /dev/null +++ b/ppf/db-mysql/README @@ -0,0 +1,213 @@ +== db_init == + +db_init is a simple shell script that creates a SQLite BitTorrent message +logging database file. It uses the ../sql/p2p-log-sqlite.sql script. The +database tables are empty except for the btclients table. The latter is +initialized to standard BitTorrent clients information in the SQL script. + +The user needs to pass the database file name to the db_init script: + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ ./db_init p2p-next.db + +== DatabaseAccess.py == + +DatabaseAccess.py is a Python script that implements low-level Python +operations for managing a BitTorrent message logging database (created +through the use of the db_init script). + +DatabaseAccess.py exports methods allowing insertion, selection and deletion +of table entries in the database. This methods are directly used by the +DatabaseCommander.py and DatabaseWriter.py scripts (see below). + +DatabaseAccess.py can be called from the command line (i.e. it implements a +main method). This enables a small test case of the exported functions and +fills the database initial entries. + +--- +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseAccess.py +('[swarms]An error ocurred: ', 'constraint failed') +('[btclients]An error ocurred: ', 'constraint failed') +('[status_messages]An error ocurred: ', 'constraint failed') +('[verbose_messages]An error ocurred: ', 'constraint failed') +(1, u'DarkKnight', 123000, u'experiment', u'TVTorrents') +(2, u'Fedora', 1024, u'experiment', u'local') +(3, u'Pulp Fiction', 102400, u'streaming', u'isohunt') +(1, u'Tribler', u'Python', 1, 1) +(2, u'libtorrent', u'C++', 1, 0) +(3, u'Vuze', u'Java', 1, 1) +(4, u'Transmission', u'C', 1, 0) +(5, u'Aria', u'C', 1, 0) +(6, u'Mainline', u'Python', 1, 0) +(7, u'Tribler', u'Python', 1, 1) +(8, u'libtorrent', u'C++', 1, 0) +(9, u'Vuze', u'Java', 1, 0) +(1, 1, 2, u'Linux', u'2.6.30', 256, 1833, u'0.0.0.0', 6969, 256, 96, 123131.1231) +(2, 3, 4, u'Linux', u'2.6.30', 256, 1833, u'0.0.0.0', 6969, 256, 96, 123131.1231) +(1, 2455128.1000000001, 222, 0, 213, 56, 200, 300, 121.324) +(1, 2455128.1212958111, u'127.0.0.1', 1345, 0, 3, 4, 13, 777) +(1, u'Tribler', u'Python', 1, 1) +(7, u'Tribler', u'Python', 1, 1) +--- + +== DatabaseCommander.py == + +DatabaseCommander.py is a Python script that allows command line access to and +interraction with the BitTorrent information database. It allows adding, +deleting and showing entries in the swarms and client_sessions table. + +The database has to be created using the db_init script and has to be used +as a final argument to the command line program. + +If swarm/session id is -1 all swarms are deleted/shown. + +If action is "add", standard input is used for reading required arguments. + +DatabaseCommander.py calls DatabaseAccess.py. + +A sample run is shown below: + +--- +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseCommander.py --add swarm p2p-next.db +swarm name (torrent filename without .torrent extension): TestSwarm + file size (in bytes): 12345678 + purpose (what is this warm used for): test + source (URL for downloading .torrent file or "local"): local + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseCommander.py --list swarm --id -1 p2p-next.db +(4, u'DarkKnight', 123000, u'experiment', u'TVTorrents') +(5, u'Fedora', 1024, u'experiment', u'local') +(6, u'Pulp Fiction', 102400, u'streaming', u'isohunt') +(7, u'TestSwarm', 12345678, u'test', u'local') + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseCommander.py --list swarm --id 1 p2p-next.db + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseCommander.py --list swarm --id 7 p2p-next.db +(7, u'TestSwarm', 12345678, u'test', u'local') + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseCommander.py --delete swarm --id 7 p2p-next.db + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseCommander.py --list swarm --id 7 p2p-next.db + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseCommander.py --list swarm --id -1 p2p-next.db +(4, u'DarkKnight', 123000, u'experiment', u'TVTorrents') +(5, u'Fedora', 1024, u'experiment', u'local') +(6, u'Pulp Fiction', 102400, u'streaming', u'isohunt') + + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseCommander.py --add session p2p-next.db +swarm id (swarm identifier in database): 4 + client name (Tribler, libtorrent, etc.): Tribler + system OS (Linux, Windows, Mac OS X): Linux + OS version (2.6.28, 7, 10.6): 2.6.30 + system RAM (in MB): 2048 + system CPU (in KHz): 1600 + public IP (dotted decimal format): 141.85.37.1 + public port: 6789 + download speed limit (in KB/s): 512 + upload speed limit (in KB/s): 64 + start time (YYYY-MM-DD HH:MM:SS): 2009-10-28 08:42:00 + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseCommander.py --list session --id -1 p2p-next.db +(3, 4, 1, u'Linux', u'2.6.30', 2048, 1600, u'141.85.37.1', 6789, 512, 64, 2455132.8624999998) +--- + +== DatabaseWriter.py == + +DatabaseWriter.py is a python script that enables access to the +status_messages and verbose_messages tables. It offer a programmer interface +and a command line interface for adding, deleting and listing entries in +the two tables. + +The programmer interface exports six methods for adding, deletetin and listing +entries (three methods for each table). + +The command line interface is similar to the one belonging to the +DatabaseCommander. It's main actions are add (-a, --add), list (-l, --list) +and delete (-d, --delete). Entries are selected by their client session id. +This meas there is no possibility (due to the database design) to remove a +specific entry, but entries belonging to a particular client session. + +The database has to be created using the db_init script and has to be used +as a final argument to the command line program. + +One of the more interesting and useful features of DatabaseWriter.py +is the read action (-r, --read) allowing to read entries from standard input. +A separator may be specified through the -s (--sep) option. Comma (,) is +used as the default separator. This allows easy integration with a +non-Python message parser, similar to the below command + ./my_parser log_file | python DatabaseWriter.py -r verbose p2p-next.db + +DatabaseWriter.py uses DatabaseAccess.py + +A sample run of the command line interface for DatabaseWriter.py is +shown below: + +--- +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l status -i -1 p2p-next.db +(1, 2455128.1000000001, 222, 0, 213, 56, 200, 300, 121.324) +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l status -i 1 p2p-next.db +(1, 2455128.1000000001, 222, 0, 213, 56, 200, 300, 121.324) + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l verbose -i 1 p2p-next.db +(1, 2455128.1212958111, u'127.0.0.1', 1345, 0, 3, 4, 13, 777) +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l verbose -i -1 p2p-next.db +(1, 2455128.1212958111, u'127.0.0.1', 1345, 0, 3, 4, 13, 777) + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -a verbose p2p-next.db +client session id: 1 + timestamp (YYYY-MM-DD HH:MM:SS.ss): 2009-10-30 12:34:12 + peer IP address (X.Y.Z.T): 10.1.1.2 + peer port: 34567 + message_type (0, 1, 2, 3, 4, 5, 6): 3 + index: 12 + begin: 45 + length: 1024 + listen_port: 0 + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l verbose -i -1 p2p-next.db +(1, 2455128.1212958111, u'127.0.0.1', 1345, 0, 3, 4, 13, 777) +(1, 2455135.0237500002, u'10.1.1.2', 34567, 3, 12, 45, 1024, 0) + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -a status p2p-next.db +client session id: 1 + timestamp (YYYY-MM-DD HH:MM:SS.ss): 2009-10-30 12:12:12 + number of peers: 34 + dht: 4 + current download speed (KB/s): 456 + current upload speed (KB/s): 23 + download size (bytes): 123456 + upload size (bytes): 3321 + eta (XdYhZmWs; e.g. 1d12h34m12s): 13m12s + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l status -i -1 p2p-next.db +(1, 2455128.1000000001, 222, 0, 213, 56, 200, 300, 121.324) +(1, 2455135.0084722224, 34, 4, 456, 23, 123456, 3321, 792) + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -d status -i 1 p2p-next.db +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l status -i -1 p2p-next.db + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -d verbose -i 1 p2p-next.db +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l verbose -i -1 p2p-next.db + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ cat status_messages.sample.txt | python DatabaseWriter.py -r status p2p-next.db +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l status -i -1 p2p-next.db +(1, 2455135.342037037, 23, 1, 324, 43, 121323, 12132, 852) +(1, 2455135.342048611, 23, 1, 324, 43, 122323, 12232, 852) +(1, 2455135.342060185, 23, 1, 323, 43, 123323, 12332, 852) +(1, 2455135.342071759, 24, 2, 322, 44, 124323, 12432, 852) +(1, 2455135.3420833335, 24, 3, 320, 45, 125323, 12532, 852) +(1, 2455135.3420949075, 25, 3, 319, 49, 126323, 12632, 852) +(1, 2455135.3421064815, 26, 2, 306, 48, 127323, 12732, 852) +(1, 2455135.3421180556, 25, 2, 301, 48, 128323, 12932, 852) + +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ cat verbose_messages.sample.txt | python DatabaseWriter.py -r verbose p2p-next.db +razvan@valhalla:~/projects/p2p-next/cs-p2p-next/auto/db$ python DatabaseWriter.py -l verbose -i -1 p2p-next.db +(1, 2455135.0226182872, u'10.0.1.2', 5678, 1, 12, 16, 1024, 0) +(1, 2455135.0226196758, u'10.0.1.2', 5678, 2, 12, 16, 1024, 0) +(1, 2455135.0226287036, u'10.0.1.2', 5678, 3, 12, 16, 1024, 0) +(1, 2455135.0226305556, u'10.0.1.2', 5678, 2, 12, 16, 1024, 0) +(1, 2455135.0226306715, u'10.0.1.2', 5678, 4, 12, 16, 1024, 0) +(1, 2455135.0226402776, u'10.0.1.2', 5678, 5, 12, 16, 1024, 0) +(1, 2455135.0226541664, u'10.0.1.2', 5678, 3, 12, 16, 1024, 0) +(1, 2455135.0226603011, u'10.0.1.2', 5678, 2, 12, 16, 1024, 0) +--- diff --git a/ppf/db-mysql/client_sessions.sample.txt b/ppf/db-mysql/client_sessions.sample.txt new file mode 100644 index 0000000..99c50e3 --- /dev/null +++ b/ppf/db-mysql/client_sessions.sample.txt @@ -0,0 +1,40 @@ +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.201, 50100, 512, 256, 2009-01-08 22:17:31 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.201, 50200, 512, 256, 2009-01-08 22:18:47 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.201, 50300, 512, 256, 2009-01-08 22:18:49 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.201, 50400, 64, 32, 2009-01-08 22:18:49 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.201, 50500, 64, 32, 2009-01-08 22:18:49 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.202, 50100, 64, 32, 2009-01-09 01:27:23 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.202, 50200, 64, 32, 2009-01-09 01:27:33 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.202, 50300, 64, 32, 2009-01-09 01:27:33 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.202, 50400, 512, 256, 2009-01-09 01:27:33 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.202, 50500, 512, 256, 2009-01-09 01:27:34 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.203, 50100, 512, 256, 2009-01-09 01:18:51 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.203, 50200, 512, 256, 2009-01-09 01:18:42 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.203, 50300, 512, 256, 2009-01-09 01:18:52 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.203, 50400, 64, 32, 2009-01-09 01:18:52 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.203, 50500, 64, 32, 2009-01-09 01:18:53 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.204, 50100, 64, 32, 2009-01-08 22:18:20 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.204, 50200, 64, 32, 2009-01-08 22:18:20 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.204, 50300, 64, 32, 2009-01-08 22:18:20 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.204, 50400, 512, 256, 2009-01-08 22:18:20 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.204, 50500, 512, 256, 2009-01-08 22:18:21 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.206, 50100, 512, 256, 2009-01-08 22:18:56 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.206, 50200, 512, 256, 2009-01-08 22:18:57 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.206, 50300, 512, 256, 2009-01-08 22:18:57 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.206, 50400, 64, 32, 2009-01-08 22:18:57 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.206, 50500, 64, 32, 2009-01-08 22:18:47 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.207, 50100, 64, 32, 2009-01-09 01:18:11 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.207, 50200, 64, 32, 2009-01-09 01:18:11 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.207, 50300, 64, 32, 2009-01-09 01:18:10 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.207, 50400, 512, 256, 2009-01-09 01:18:11 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.207, 50500, 512, 256, 2009-01-09 01:18:21 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.208, 50100, 512, 256, 2009-01-09 01:19:46 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.208, 50200, 512, 256, 2009-01-09 01:19:46 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.208, 50300, 512, 256, 2009-01-09 01:19:46 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.208, 50400, 64, 32, 2009-01-09 01:19:47 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.208, 50500, 64, 32, 2009-01-09 01:19:38 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.209, 50100, 64, 32, 2009-01-09 01:20:31 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.209, 50200, 64, 32, 2009-01-09 01:20:31 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.209, 50300, 64, 32, 2009-01-09 01:20:30 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.209, 50400, 512, 256, 2009-01-09 01:20:32 +1, libtorrent, Debian GNU/Linux, 5.0 Lenny - 2.6.26-2-openvz-amd64, 512, 6000, 141.85.224.209, 50500, 512, 256, 2009-01-09 01:20:41 diff --git a/ppf/db-mysql/db_init b/ppf/db-mysql/db_init new file mode 100755 index 0000000..0f542e7 --- /dev/null +++ b/ppf/db-mysql/db_init @@ -0,0 +1,14 @@ +#!/bin/bash + +SQL_INIT_SCRIPT=../sql/p2p-log-mysql.sql + +if test $# -ne 1; then + echo "Usage: $0 db_name" + exit 1 +fi + +DB_NAME=$1 + +mysql --user=root --password=p2p4th3m42232 $DB_NAME <../sql/p2p-log-mysql.sql + +exit 0 diff --git a/ppf/db-mysql/julian.py b/ppf/db-mysql/julian.py new file mode 100644 index 0000000..30835f8 --- /dev/null +++ b/ppf/db-mysql/julian.py @@ -0,0 +1,52 @@ +#!/usr/bin/env python + +import sys +import sidereal +from datetime import datetime, date, time + +# +# arguments are standard date string "YYYY-MM-DD" +# and standard time string "HH:MM:SS.ss..." +# + +def stringToJulian(date, time): + d = sidereal.parseDate(date) + t = sidereal.parseTime(time) + dt = datetime.combine(d, t) + return sidereal.JulianDate.fromDatetime(dt) + +def datetimeToJulian(datetime): + return sidereal.JulianDate.fromDatetime(datetime) + +# +# arguments are standard date/time fields +# + +def fieldsToJulian(year, month, day, hour, minute, second, millisecond, microsecond = 0): + d = date(year, month, day) + t = time(hour, minute, second, millisecond * 1000 + microsecond) + dt = datetime.combine(d, t) + return sidereal.JulianDate.fromDatetime(dt) + +# +# argument is a julian date number +# + +def julianToDatetime(jd): + julianDate = sidereal.JulianDate(jd) + return julianDate.datetime() + + +# +# test case for Julian Date conversion functions +# + +def main(): + jd = stringToJulian("2000-01-01", "12:00:00.00") + print "getJulianFromDatime: ", float(jd) + + jd = fieldsToJulian(2000, 1, 1, 12, 0, 0, 0, 0) + print "getJulianFromFields: ", float(jd) + +if __name__ == "__main__": + sys.exit(main()) diff --git a/ppf/db-mysql/sidereal.py b/ppf/db-mysql/sidereal.py new file mode 100644 index 0000000..ab02c2d --- /dev/null +++ b/ppf/db-mysql/sidereal.py @@ -0,0 +1,1457 @@ +"""sidereal.py: A Python module for astronomical calculations. + + For documentation, see: + http://www.nmt.edu/tcc/help/lang/python/examples/sidereal/ims/ +""" +#================================================================ +# Imports +#---------------------------------------------------------------- + +from math import * +import re +import datetime +#================================================================ +# Manifest constants +#---------------------------------------------------------------- + +FIRST_GREGORIAN_YEAR = 1583 +TWO_PI = 2.0 * pi +PI_OVER_12 = pi / 12.0 +JULIAN_BIAS = 2200000 # 2,200,000 +SIDEREAL_A = 0.0657098 +FLOAT_PAT = re.compile ( + r'\d+' # Matches one or more digits + r'(' # Start optional fraction + r'[.]' # Matches the decimal point + r'\d+' # Matches one or more digits + r')?' ) # End optional group +D_PAT = re.compile ( r'[dD]' ) +M_PAT = re.compile ( r'[mM]' ) +S_PAT = re.compile ( r'[sS]' ) +H_PAT = re.compile ( r'[hH]' ) +NS_PAT = re.compile ( r'[nNsS]' ) +EW_PAT = re.compile ( r'[eEwW]' ) +# - - - h o u r s T o R a d i a n s + +def hoursToRadians ( hours ): + """Convert hours (15 degrees) to radians. + """ + return hours * PI_OVER_12 +# - - - r a d i a n s T o H o u r s + +def radiansToHours ( radians ): + """Convert radians to hours (15 degrees). + """ + return radians / PI_OVER_12 +# - - - h o u r A n g l e T o R A + +def hourAngleToRA ( h, ut, eLong ): + """Convert hour angle to right ascension. + + [ (h is an hour angle in radians as a float) and + (ut is a timestamp as a datetime.datetime instance) and + (eLong is an east longitude in radians) -> + return the right ascension in radians corresponding + to that hour angle at that time and location ] + """ + #-- 1 -- + # [ gst := the Greenwich Sidereal Time equivalent to + # ut, as a SiderealTime instance ] + gst = SiderealTime.fromDatetime ( ut ) + #-- 2 -- + # [ lst := the local time corresponding to gst at + # longitude eLong ] + lst = gst.lst ( eLong ) + #-- 3 -- + # [ alpha := lst - h, normalized to [0,2*pi) ] + alpha = (lst.radians - h) % TWO_PI + + #-- 4 -- + return alpha +# - - - r a T o H o u r A n g l e + +def raToHourAngle ( ra, ut, eLong ): + """Convert right ascension to hour angle. + + [ (ra is a right ascension in radians as a float) and + (ut is a timestamp as a datetime.datetime instance) and + (eLong is an east longitude in radians) -> + return the hour angle in radians at that time and + location corresponding to that right ascension ] + """ + #-- 1 -- + # [ gst := the Greenwich Sidereal Time equivalent to + # ut, as a SiderealTime instance ] + gst = SiderealTime.fromDatetime ( ut ) + + #-- 2 -- + # [ lst := the local time corresponding to gst at + # longitude eLong ] + lst = gst.lst ( eLong ) + #-- 3 -- + # [ h := lst - ra, normalized to [0,2*pi) ] + h = (lst.radians - ra) % TWO_PI + + #-- 4 -- + return h +# - - - d a y N o + +def dayNo ( dt ): + """Compute the day number within the year. + + [ dt is a date as a datetime.datetime or datetime.date -> + return the number of days between dt and Dec. 31 of + the preceding year ] + """ + #-- 1 -- + # [ dateOrd := proleptic Gregorian ordinal of dt + # jan1Ord := proleptic Gregorian ordinal of January 1 + # of year (dt.year) ] + dateOrd = dt.toordinal() + jan1Ord = datetime.date ( dt.year, 1, 1 ).toordinal() + + #-- 2 -- + return dateOrd - jan1Ord + 1 +# - - - p a r s e D a t e t i m e + +T_PATTERN = re.compile ( '[tT]' ) + +def parseDatetime ( s ): + """Parse a date with optional time. + + [ s is a string -> + if s is a valid date with optional time -> + return that timestamp as a datetime.datetime instance + else -> raise SyntaxError ] + """ + #-- 1 -- + # [ if s contains "T" or "t" -> + # rawDate := s up to the first such character + # rawTime := s from just after the first such + # character to the end + # else -> + # rawDate := s + # rawTime := None ] + m = T_PATTERN.search ( s ) + if m is None: + rawDate, rawTime = s, None + else: + rawDate = s[:m.start()] + rawTime = s[m.end():] + #-- 2 -- + # [ if rawDate is a valid date -> + # datePart := rawDate as a datetime.datetime instance + # else -> raise SyntaxError ] + datePart = parseDate ( rawDate ) + #-- 3 -- + # [ if rawTime is None -> + # timePart := 00:00 as a datetime.time + # else if rawTime is valid -> + # timePart := rawTime as a datetime.time + # else -> raise SyntaxError ] + if rawTime is None: + timePart = datetime.time ( 0, 0 ) + else: + timePart = parseTime ( rawTime ) + #-- 4 -- + return datetime.datetime.combine ( datePart, timePart ) +# - - - p a r s e D a t e + +YEAR_FIELD = "Y" +MONTH_FIELD = "M" +DAY_FIELD = "D" + +dateRe = ( + r'(' # Begin YEAR_FIELD + r'?P<%s>' # Name this group YEAR_FIELD + r'\d{4}' # Match exactly four digits + r')' # End YEAR_FIELD + r'\-' # Matches one hyphen + r'(' # Begin MONTH_FIELD + r'?P<%s>' # Name this group MONTH_FIELD + r'\d{1,2}' # Matches one or two digits + r')' # End MONTH_FIELD + r'\-' # Matches "-" + r'(' # Begin DAY_FIELD + r'?P<%s>' # Name this group DAY_FIELD + r'\d{1,2}' # Matches one or two digits + r')' # End DAY_FIELD + r'$' # Make sure all characters match + ) % (YEAR_FIELD, MONTH_FIELD, DAY_FIELD) +DATE_PAT = re.compile ( dateRe ) + +def parseDate ( s ): + """Validate and convert a date in external form. + + [ s is a string -> + if s is a valid external date string -> + return that date as a datetime.date instance + else -> raise SyntaxError ] + """ + #-- 1 -- + # [ if DATE_PAT matches s -> + # m := a match instance describing the match + # else -> raise SyntaxError ] + m = DATE_PAT.match ( s ) + if m is None: + raise SyntaxError, ( "Date does not have pattern YYYY-DD-MM: " + "'%s'" % s ) + #-- 2 -- + year = int ( m.group ( YEAR_FIELD ) ) + month = int ( m.group ( MONTH_FIELD ) ) + day = int ( m.group ( DAY_FIELD ) ) + + #-- 3 -- + return datetime.date ( year, month, day ) +# - - - p a r s e T i m e + +def parseTime ( s ): + """Validate and convert a time and optional zone. + + [ s is a string -> + if s is a valid time with optional zone suffix -> + return that time as a datetime.time + else -> raise SyntaxError ] + """ + #-- 1 - + # [ if s starts with FLOAT_PAT -> + # decHour := matching part of s as a float + # minuteTail := part s past the match + # else -> raise SyntaxError ] + decHour, minuteTail = parseFloat ( s, "Hour number" ) + #-- 2 -- + # [ if minuteTail starts with ":" followed by FLOAT_PAT -> + # decMinute := part matching FLOAT_PAT as a float + # secondTail := part of minuteTail after the match + # else if minuteTail starts with ":" not followed by + # FLOAT_PAT -> + # raise SyntaxError + # else -> + # decMinute := 0.0 + # secondTail := minuteTail ] + if minuteTail.startswith(':'): + m = FLOAT_PAT.match ( minuteTail[1:] ) + if m is None: + raise SyntaxError, ( "Expecting minutes: '%s'" % + minuteTail ) + else: + decMinute = float(m.group()) + secondTail = minuteTail[m.end()+1:] + else: + decMinute = 0.0 + secondTail = minuteTail + #-- 3 -- + # [ if secondTail starts with ":" followed by FLOAT_PAT -> + # decSecond := part matching FLOAT_PAT as a float + # zoneTail := part of secondTail after the match + # else if secondTail starts with ":" not followed by + # FLOAT_PAT -> + # raise SyntaxError + # else -> + # decSecond := 0.0 + # zoneTail := secondTail ] + if secondTail.startswith(':'): + m = FLOAT_PAT.match ( secondTail[1:] ) + if m is None: + raise SyntaxError, ( "Expecting seconds: '%s'" % + secondTail ) + else: + decSecond = float(m.group()) + zoneTail = secondTail[m.end()+1:] + else: + decSecond = 0.0 + zoneTail = secondTail + #-- 4 -- + # [ if zoneTail is empty -> + # tz := None + # else if zoneTail is a valid zone suffix -> + # tz := that zone information as an instance of a class + # that inherits from datetime.tzinfo + # else -> raise SyntaxError ] + if len(zoneTail) == 0: + tz = None + else: + tz = parseZone ( zoneTail ) + #-- 5 -- + # [ hours := decHour + decMinute/60.0 + decSecond/3600.0 ] + hours = dmsUnits.mixToSingle ( (decHour, decMinute, decSecond) ) + #-- 6 -- + # [ return a datetime.time representing hours ] + hh, mm, seconds = dmsUnits.singleToMix ( hours ) + wholeSeconds, fracSeconds = divmod ( seconds, 1.0 ) + ss = int(wholeSeconds) + usec = int ( fracSeconds * 1e6 ) + return datetime.time ( hh, mm, ss, usec, tz ) +# - - - p a r s e Z o n e + +def parseZone ( s ): + """Validate and convert a time zone suffix. + + [ s is a string -> + if s is a valid time zone suffix -> + return that zone's information as an instance of + a class that inherits from datetime.tzinfo + else -> raise SyntaxError ] + """ + #-- 1 -- + # [ if s starts with "+" or "-" and is a valid fixed-offset + # time zone suffix -> + # return that zone's information as a datetime.tzinfo instance + # else if is starts with "+" or "-" but is not a valid + # fixed-offset time zone suffix -> + # raise SyntaxError + # else -> I ] + if s.startswith("+") or s.startswith("-"): + return parseFixedZone ( s ) + + #-- 2 -- + # [ if s.upper() is a key in zoneCodeMap -> + # return the corresponding value + # else -> raise SyntaxError ] + try: + tz = zoneCodeMap[s.upper()] + return tz + except KeyError: + raise SyntaxError, ( "Unknown time zone code: '%s'" % s ) +# - - - p a r s e F i x e d Z o n e + +HHMM_PAT = re.compile ( + r'\d{4}' # Matches exactly four digits + r'$' ) # Be sure everything is matched + +def parseFixedZone ( s ): + """Convert a +hhmm or -hhmm zone suffix. + + [ s is a string -> + if s is a time zone suffix of the form "+hhmm" or "-hhmm" -> + return that zone information as an instance of a class + that inherits from datetime.tzinfo + else -> raise SyntaxError ] + """ + #-- 1 -- + if s.startswith('+'): sign = 1 + elif s.startswith('-'): sign = -1 + else: + raise SyntaxError, ( "Expecting zone modifier as %shhmm: " + "'%s'" % (s[0], s) ) + #-- 2 -- + # [ if s[1:] matches HHMM_PAT -> + # hours := the HH part as an int + # minutes := the MM part as an int + # else -> raise SyntaxError ] + rawHHMM = s[1:] + m = HHMM_PAT.match ( rawHHMM ) + if m is None: + raise SyntaxError, ( "Expecting zone modifier as %sHHMM: " + "'%s'" % (s[0], s) ) + else: + hours = int ( rawHHMM[:2] ) + minutes = int ( rawHHMM[2:] ) + + #-- 3 -- + return FixedZone ( sign*hours, sign*minutes, s ) + +# - - - - - c l a s s F i x e d Z o n e + +DELTA_ZERO = datetime.timedelta(0) +DELTA_HOUR = datetime.timedelta(hours=1) + +class FixedZone(datetime.tzinfo): + """Represents a time zone with a fixed offset east of UTC. + + Exports: + FixedZone ( hours, minutes, name ): + [ (hours is a signed offset in hours as an int) and + (minutes is a signed offset in minutes as an int) -> + return a new FixedZone instance representing + those offsets east of UTC ] + State/Invariants: + .__offset: + [ a datetime.timedelta representing self's offset + east of UTC ] + .__name: + [ as passed to the constructor's name argument ] + """ + def __init__ ( self, hh, mm, name ): + """Constructor for FixedZone. + """ + self.__offset = datetime.timedelta ( hours=hh, minutes=mm ) + self.__name = name + def utcoffset(self, dt): + """Return self's offset east of UTC. + """ + return self.__offset + def tzname(self, dt): + """Return self's name. + """ + return self.__name + def dst(self, dt): + """Return self's daylight time offset. + """ + return DELTA_ZERO +def firstSundayOnOrAfter ( dt ): + """Find the first Sunday on or after a given date. + + [ dt is a datetime.date -> + return a datetime.date representing the first Sunday + on or after dt ] + """ + daysToGo = dt.weekday() + if daysToGo: + dt += datetime.timedelta ( daysToGo ) + return dt + +# - - - - - c l a s s U S T i m e Z o n e + +class USTimeZone(datetime.tzinfo): + """Represents a U.S. time zone, with automatic daylight time. + + Exports: + USTimeZone ( hh, mm, name, stdName, dstName ): + [ (hh is an offset east of UTC in hours) and + (mm is an offset east of UTC in minutes) and + (name is the composite zone name) and + (stdName is the non-DST name) and + (dstName is the DST name) -> + return a new USTimeZone instance with those values ] + + State/Invariants: + .__offset: + [ self's offset east of UTC as a datetime.timedelta ] + .__name: [ as passed to constructor's name ] + .__stdName: [ as passed to constructor's stdName ] + .__dstName: [ as passed to constructor's dstName ] + """ + DST_START_OLD = datetime.datetime ( 1, 4, 1, 2 ) + DST_END_OLD = datetime.datetime ( 1, 10, 25, 2 ) + DST_START_2007 = datetime.datetime ( 1, 3, 8, 2 ) + DST_END_2007 = datetime.datetime ( 1, 11, 1, 2 ) + def __init__ ( self, hh, mm, name, stdName, dstName ): + self.__offset = datetime.timedelta ( hours=hh, minutes=mm ) + self.__name = name + self.__stdName = stdName + self.__dstname = dstName + def tzname(self, dt): + if self.dst(dt): return self.__dstName + else: return self.__stdName + def utcoffset(self, dt): + return self.__offset + self.dst(dt) + def dst(self, dt): + """Return the current DST offset. + + [ dt is a datetime.date -> + if daylight time is in effect in self's zone on + date dt -> + return +1 hour as a datetime.timedelta + else -> + return 0 as a datetime.delta ] + """ + #-- 1 -- + # [ dtStart := Sunday when DST starts in year dt.year + # dtEnd := Sunday when DST ends in year dt.year ] + if dt.year >= 2007: + startDate = self.DST_START_2007.replace ( year=dt.year ) + endDate = self.DST_END_2007.replace ( year=dt.year ) + else: + startDate = self.DST_START_OLD.replace ( year=dt.year ) + endDate = self.DST_END_OLD.replace ( year=dt.year ) + dtStart = firstSundayOnOrAfter ( startDate ) + dtEnd = firstSundayOnOrAfter ( endDate ) + #-- 2 -- + # [ naiveDate := dt with its tzinfo member set to None ] + naiveDate = dt.replace ( tzinfo=None ) + #-- 3 -- + # [ if naiveDate is in the interval (dtStart, dtEnd) -> + # return DELTA_HOUR + # else -> + # return DELTA_ZERO ] + if dtStart <= naiveDate < dtEnd: + return DELTA_HOUR + else: + return DELTA_ZERO +utcZone = FixedZone(0, 0, "UTC") + +estZone = FixedZone(-5, 0, "EST") +edtZone = FixedZone(-4, 0, "EDT") +etZone = USTimeZone(-5, 0, "ET", "EST", "EDT") + +cstZone = FixedZone(-6, 0, "CST") +cdtZone = FixedZone(-5, 0, "CDT") +ctZone = USTimeZone(-6, 0, "CT", "CST", "CDT") + +mstZone = FixedZone(-7, 0, "MST") +mdtZone = FixedZone(-6, 0, "MDT") +mtZone = USTimeZone(-7, 0, "MT", "MST", "MDT") + +pstZone = FixedZone(-8, 0, "PST") +pdtZone = FixedZone(-7, 0, "PDT") +ptZone = USTimeZone(-8, 0, "PT", "PST", "PDT") + +zoneCodeMap = { + "UTC": utcZone, + "EST": estZone, "EDT": edtZone, "ET": etZone, + "CST": cstZone, "CDT": cdtZone, "CT": ctZone, + "MST": mstZone, "MDT": mdtZone, "MT": mtZone, + "PST": pstZone, "PDT": pdtZone, "PT": ptZone } +# - - - p a r s e A n g l e + +def parseAngle ( s ): + """Validate and convert an external angle. + + [ s is a string -> + if s is a valid external angle -> + return s in radians + else -> raise SyntaxError ] + """ + #-- 1 -- + minute = second = 0.0 + #-- 2 -- + # [ if s starts with a float followed by 'd' or 'D' -> + # degree := that float as type float + # minTail := s after that float and suffix + # else -> raise SyntaxError ] + degree, minTail = parseFloatSuffix ( s, D_PAT, + "Degrees followed by 'd'" ) + + #-- 3 -- + # [ if minTail is empty -> I + # else if minTail has the form "(float)m" -> + # minute := that (float) + # else if minTail has the form "(float)m(float)s" -> + # minute := the first (float) + # second := the second (float) + # else -> raise SyntaxError ] + if len(minTail) != 0: + #-- 3.1 -- + # [ if minTail starts with a float followed by 'm' or 'M' -> + # minute := that float as type float + # secTail := minTail after all that + # else -> raise SyntaxError ] + minute, secTail = parseFloatSuffix ( minTail, M_PAT, + "Minutes followed by 'm'" ) + + #-- 3.2 -- + # [ if secTail is empty -> I + # else if secTail starts with a float followed by + # 's' or 'S' -> + # second := that float as type float + # checkTail := secTail after all that + # else -> raise SyntaxError ] + if len(secTail) != 0: + second, checkTail = parseFloatSuffix ( secTail, + S_PAT, "Seconds followed by 's'" ) + if len(checkTail) != 0: + raise SyntaxError, ( "Unidentifiable angle parts: " + "'%s'" % checkTail ) + #-- 4 -- + # [ return the angle (degree, minute, second) in radians ] + angleDegrees = dmsUnits.mixToSingle ( (degree, minute, second) ) + return radians ( angleDegrees ) +# - - - p a r s e F l o a t S u f f i x + +def parseFloatSuffix ( s, codeRe, message ): + """Parse a float followed by a letter code. + + [ (s is a string) and + (codeRe is a compiled regular expression) and + (message is a string describing what is expected) -> + if s starts with a float, followed by code (using + case-insensitive comparison) -> + return (x, tail) where x is that float as type float + and tail is the part of s after the float and code + else -> raise SyntaxError, "Expecting (message)" ] + """ + #-- 1 -- + # [ if s starts with a float -> + # x := that float as type float + # codeTail := the part of s after that float + # else -> raise SyntaxError, "Expecting (message)" ] + x, codeTail = parseFloat ( s, message ) + + #-- 2 -- + # [ if codeTail starts with code (case-insensitive) -> + # return (x, the part of codeTail after the match) + # else -> raise SyntaxError ] + discard, tail = parseRe ( codeTail, codeRe, message ) + + #-- 3 -- + return (x, tail) +# - - - p a r s e F l o a t + +def parseFloat ( s, message ): + """Parse a floating-point number at the front of s. + + [ (s is a string) and + (message is a string describing what is expected) -> + if s begins with a floating-point number -> + return (x, tail) where x is the number as type float + and tail is the part of s after the match + else -> raise SyntaxError, "Expecting (message)" ] + """ + #-- 1 -- + # [ if the front of s matches FLOAT_PAT -> + # m := a Match object describing the match + # else -> raise SyntaxError ] + rawFloat, tail = parseRe ( s, FLOAT_PAT, message ) + + #-- 2 -- + return (float(rawFloat), tail) +# - - - p a r s e R e + +def parseRe ( s, regex, message ): + """Parse a regular expression at the head of a string. + + [ (s is a string) and + (regex is a compiled regular expression) and + (message is a string describing what is expected) -> + if s starts with a string that matches regex -> + return (head, tail) where head is the part of s + that matched and tail is the rest + else -> + raise SyntaxError, "Expecting (message)" ] + """ + + #-- 1 -- + # [ if the head of s matches regex -> + # m := a match object describing the matching part + # else -> raise SyntaxError, "Expecting (message)" ] + m = regex.match ( s ) + if m is None: + raise SyntaxError, "Expecting %s: '%s'" % (message, s) + #-- 2 -- + # [ return (matched text from s, text from s after match) ] + head = m.group() + tail = s[m.end():] + return (head, tail) +# - - - p a r s e L a t + +def parseLat ( s ): + """Validate and convert an external latitude. + + [ s is a nonempty string -> + if s is a valid external latitude -> + return that latitude in radians + else -> raise SyntaxError ] + """ + #-- 1 -- + # [ last := last character of s + # rawAngle := s up to the last character ] + last = s[-1] + rawAngle = s[:-1] + + #-- 2 -- + # [ if last matches NS_PAT -> + # nsFlag := last, lowercased + # else -> raise SyntaxError ] + m = NS_PAT.match ( last ) + if m is None: + raise SyntaxError, ( "Latitude '%s' does not end with 'n' " + "or 's'." % s ) + else: + nsFlag = last.lower() + #-- 3 -- + # [ if rawAngle is a valid angle -> + # absAngle := that angle in radians + # else -> raise SyntaxError ] + absAngle = parseAngle ( rawAngle ) + #-- 4 -- + if nsFlag == 's': angle = - absAngle + else: angle = absAngle + + #-- 5 -- + return angle +# - - - p a r s e L o n + +def parseLon ( s ): + """Validate and convert an external longitude. + + [ s is a nonempty string -> + if s is a valid external longitude -> + return that longitude in radians + else -> raise SyntaxError ] + """ + #-- 1 -- + # [ last := last character of s + # rawAngle := s up to the last character ] + last = s[-1] + rawAngle = s[:-1] + + #-- 2 -- + # [ if EW_PAT matches last -> + # ewFlag := last, lowercased + # else -> raise SyntaxError ] + m = EW_PAT.match ( last ) + if m is None: + raise SyntaxError, ( "Longitude '%s' does not end with " + "'e' or 'w'." % s ) + else: + ewFlag = last.lower() + #-- 3 -- + # [ if rawAngle is a valid angle -> + # absAngle := that angle in radians + # else -> raise SyntaxError ] + absAngle = parseAngle ( rawAngle ) + #-- 4 -- + if ewFlag == 'w': angle = TWO_PI - absAngle + else: angle = absAngle + + #-- 5 -- + return angle +# - - - p a r s e H o u r s + +def parseHours ( s ): + """Validate and convert a quantity in hours. + + [ s is a non-empty string -> + if s is a valid mixed hours expression -> + return the value of s as decimal hours + else -> raise SyntaxError ] + """ + #-- 1 -- + minute = second = 0.0 + + #-- 2 -- + # [ if s starts with a float followed by 'h' or 'H' -> + # hour := that float as type float + # minTail := s after that float and suffix + # else -> raise SyntaxError ] + hour, minTail = parseFloatSuffix ( s, H_PAT, + "Hours followed by 'h'" ) + + #-- 3 -- + # [ if minTail is empty -> I + # else if minTail has the form "(float)m" -> + # minute := that (float) + # else if minTail has the form "(float)m(float)s" -> + # minute := the first (float) + # second := the second (float) + # else -> raise SyntaxError ] + if len(minTail) != 0: + #-- 3.1 -- + # [ if minTail starts with a float followed by 'm' or 'M' -> + # minute := that float as type float + # secTail := minTail after all that + # else -> raise SyntaxError ] + minute, secTail = parseFloatSuffix ( minTail, M_PAT, + "Minutes followed by 'm'" ) + + #-- 3.2 -- + # [ if secTail is empty -> I + # else if secTail starts with a float followed by + # 's' or 'S' -> + # second := that float as type float + # checkTail := secTail after all that + # else -> raise SyntaxError ] + if len(secTail) != 0: + second, checkTail = parseFloatSuffix ( secTail, + S_PAT, "Seconds followed by 's'" ) + if len(checkTail) != 0: + raise SyntaxError, ( "Unidentifiable angle parts: " + "'%s'" % checkTail ) + #-- 4 -- + # [ return the quantity (hour, minute, second) in hours ] + result = dmsUnits.mixToSingle ( (hour, minute, second) ) + return result + +# - - - - - c l a s s M i x e d U n i t s + +class MixedUnits: + """Represents a system with mixed units, e.g., hours/minutes/seconds + """ +# - - - M i x e d U n i t s . _ _ i n i t _ _ + + def __init__ ( self, factors ): + """Constructor + """ + self.factors = factors +# - - - M i x e d U n i t s . m i x T o S i n g l e + + def mixToSingle ( self, coeffs ): + """Convert mixed units to a single value. + + [ coeffs is a sequence of numbers not longer than + len(self.factors)+1 -> + return the equivalent single value in self's system ] + """ + #-- 1 -- + total = 0.0 + + #-- 2 -- + # [ if len(coeffs) <= len(self.factors)+1 -> + # coeffList := a copy of coeffs, right-padded to length + # len(self.factors)+1 with zeroes if necessary ] + coeffList = self.__pad ( coeffs ) + #-- 3 -- + # [ total +:= (coeffList[-1] * + # (product of all elements of self.factors)) + + # (coeffList[-2] * + # (product of all elements of self.factors[:-1])) + + # (coeffList[-3] * + # (product of all elements of self.factors[:-2])) + # ... ] + for i in range ( -1, -len(self.factors)-1, -1): + total += coeffList[i] + total /= self.factors[i] + #-- 4 -- + total += coeffList[0] + + #-- 5 -- + return total +# - - - M i x e d U n i t s . _ _ p a d + + def __pad ( self, coeffs ): + """Pad coefficient lists to standard length. + + [ coeffs is a sequence of numbers -> + if len(coeffs) > len(self.factors)+1 -> + raise ValueError + else -> + return a list containing the elements of coeff, + plus additional zeroes on the right if necessary + so that the result has length len(self.factors)+1 ] + """ + #-- 1 -- + # [ stdLen := 1 + len(self.factors) + # shortage := 1 + len(self.factors) - len(coeffs) + # result := a copy of coeffs as a list ] + stdLen = 1 + len(self.factors) + shortage = stdLen - len(coeffs) + result = list(coeffs) + + #-- 2 -- + # [ if shortage < 0 -> + # raise ValueError + # else -> + # result := result + (a list of shortage zeroes) ] + if shortage < 0: + raise ValueError, ( "Value %s has too many elements; " + "max is %d." % (coeffs, stdLen) ) + elif shortage > 0: + result += [0.0] * shortage + + #-- 3 -- + return result +# - - - M i x e d U n i t s . s i n g l e T o M i x + + def singleToMix ( self, value ): + """Convert to mixed units. + + [ value is a float -> + return value as a sequence of coefficients in + self's system ] + """ + #-- 1 -- + # [ whole := whole part of value + # frac := fractional part of value ] + whole, frac = divmod ( value, 1.0 ) + result = [int(whole)] + #-- 2 -- + # [ result := result with integral parts of value + # in self's system appended ] + for factorx in range(len(self.factors)): + frac *= self.factors[factorx] + whole, frac = divmod ( frac, 1.0 ) + result.append ( int(whole) ) + #-- 3 -- + # [ result := result with frac added to its last element ] + result[-1] += frac + + #-- 4 -- + return result +# - - - M i x e d U n i t s . f o r m a t + + def format ( self, coeffs, decimals=0, lz=False ): + """Format mixed units. + + [ (coeffs is a sequence of numbers as returned by + MixedUnits.singleToMix()) and + (decimals is a nonnegative integer) and + (lz is a bool) -> + return a list of strings corresponding to the values + of coeffs, with all the values but the last formatted + as integers, all values zero padded iff lz is true, + and the last value with (decimals) digits after the + decimal point ] + """ + #-- 1 -- + coeffList = self.__pad ( coeffs ) + + #-- 2 -- + # [ result := the values from coeffList[:-1] formatted + # as integers ] + if lz: fmt = "%02d" + else: fmt = "%d" + result = [ fmt % x + for x in coeffList[:-1] ] + #-- 2 -- + # [ whole := whole part of coeffList[-1] + # frac := fractional part of coeffList[-1] + # fuzz := 0.5 * (10 ** (-decimals) ] + whole, frac = divmod ( float(coeffList[-1]), 1.0 ) + fuzz = 0.5 * (10.0 ** (-decimals)) + #-- 3 -- + # [ if frac >= (1-fuzz) -> + # result +:= [whole+frac-fuzz], formatted with + # (decimals) digits after the decimal + # else -> + # result += coeffList[-1], formatted with (decimals) + # digits after the decimal ] + if frac >= (1.0-fuzz): + corrected = whole + frac - fuzz + else: + corrected = coeffList[-1] + #-- 4 -- + # [ if lz -> + # s := corrected, formatted with 2 digits of left-zero + # padding and (decimals) precision + # else -> + # s := corrected, formatted with (decimals) precision ] + if lz: + if decimals: n = decimals+3 + else: n = decimals+2 + + s = "%0*.*f" % (n, decimals, corrected) + else: + s = "%.*f" % (decimals, corrected) + + #-- 5 -- + result.append ( s ) + + #-- 6 -- + return result +dmsUnits = MixedUnits ( (60, 60) ) + +# - - - - - c l a s s L a t L o n + +class LatLon: + """Represents a latitude+longitude. + """ +# - - - L a t L o n . _ _ i n i t _ _ + + def __init__ ( self, lat, lon ): + """Constructor for LatLon. + """ + self.lat = lat + self.lon = lon % TWO_PI +# - - - L a t L o n . _ _ s t r _ _ + + def __str__ ( self ): + """Return self as a string. + """ + #-- 1 -- + if self.lon >= pi: + e_w = "W" + lonDeg = degrees ( TWO_PI - self.lon ) + else: + e_w = "E" + lonDeg = degrees ( self.lon ) + + #-- 2 -- + if self.lat < 0: + n_s = "S" + latDeg = degrees ( - self.lat ) + else: + n_s = "N" + latDeg = degrees ( self.lat ) + #-- 3 -- + # [ latList := three formatted values of latDeg in + # degrees/minutes/seconds + # lonList := three formatted values of lonDeg similarly ] + latList = dmsUnits.format ( dmsUnits.singleToMix(latDeg), 1 ) + lonList = dmsUnits.format ( dmsUnits.singleToMix(lonDeg), 1 ) + + #-- 4 -- + return ( '[%sd %s\' %s" %s Lat %sd %s\' %s" %s Lon]' % + (latList[0], latList[1], latList[2], n_s, + lonList[0], lonList[1], lonList[2], e_w) ) + +# - - - - - c l a s s J u l i a n D a t e + +class JulianDate: + """Class to represent Julian-date timestamps. + + State/Invariants: + .f: [ (Julian date as a float) - JULIAN_BIAS ] + """ +# - - - J u l i a n D a t e . _ _ i n i t _ _ + + def __init__ ( self, j, f=0.0 ): + """Constructor for JulianDate. + """ + self.j = j - JULIAN_BIAS + f +# - - - J u l i a n D a t e . _ _ f l o a t _ _ + + def __float__ ( self ): + """Convert self to a float. + """ + return self.j + JULIAN_BIAS +# - - - J u l i a n D a t e . d a t e t i m e + + def datetime ( self ): + """Convert to a standard Python datetime object in UT. + """ + #-- 1 -- + # [ i := int(self.j + 0.5) + # f := (self.j + 0.5) % 1.0 ] + i, f = divmod ( self.j + 0.5, 1.0 ) + i += JULIAN_BIAS + #-- 2 -- + if i > 2299160: + a = int((i-1867216.25)/36524.25) + b = i + 1 + a - int ( a / 4.0 ) + else: + b = i + #-- 3 -- + c = b + 1524 + #-- 4 -- + d = int((c-122.1)/365.25) + #-- 5 -- + e = int(365.25*d) + #-- 6 -- + g = int((c-e)/30.6001) + #-- 7 -- + dayFrac = c - e + f - int ( 30.6001 * g ) + day, frac = divmod ( dayFrac, 1.0 ) + dd = int(day) + hr, mn, sc = dmsUnits.singleToMix ( 24.0*frac ) + #-- 8 -- + if g < 13.5: mm = int(g - 1) + else: mm = int(g - 13) + #-- 9 -- + if mm > 2.5: yyyy = int(d-4716) + else: yyyy = int(d-4715) + #-- 10 -- + sec, fracSec = divmod(sc, 1.0) + usec = int(fracSec * 1e6) + return datetime.datetime ( yyyy, mm, dd, hr, mn, int(sec), + usec ) +# - - - J u l i a n D a t e . o f f s e t + + def offset ( self, delta ): + """Return a new JulianDate for self+(delta days) + + [ delta is a number of days as a float -> + return a new JulianDate (delta) days in the + future, or past if negative ] + """ + #-- 1 -- + newJ = self.j + delta + + #-- 2 -- + # [ newWhole := whole part of newJ + # newFrac := fractional part of newJ ] + newWhole, newFrac = divmod ( newJ ) + + #-- 3 -- + return JulianDate ( newWhole+JULIAN_BIAS, newFrac ) +# - - - J u l i a n D a t e . _ _ s u b _ _ + + def __sub__ ( self, other ): + """Implement subtraction. + + [ other is a JulianDate instance -> + return self.j - other.j ] + """ + return self.j - other.j +# - - - J u l i a n D a t e . _ _ c m p _ _ + + def __cmp__ ( self, other ): + """Compare two instances. + + [ other is a JulianDate instance -> + if self.j < other.j -> return a negative number + else if self.j == other.j -> return zero + else -> return a positive number ] + """ + return cmp ( self.j, other.j ) +# - - - J u l i a n D a t e . f r o m D a t e t i m e + +# @staticmethod + def fromDatetime ( dt ): + """Create a JulianDate instance from a datetime.datetime. + + [ dt is a datetime.datetime instance -> + if dt is naive -> + return the equivalent new JulianDate instance, + assuming dt expresses UTC + else -> + return a new JulianDate instance for the UTC + time equivalent to dt ] + """ + #-- 1 -- + # [ if dt is naive -> + # utc := dt + # else -> + # utc := dt - dt.utcoffset() ] + utc = dt + offset = dt.utcoffset() + if offset: + utc = dt - offset + #-- 2 -- + # [ fracDay := fraction of a day in [0.0,1.0) made from + # utc.hour, utc.minute, utc.second, and utc.microsecond ] + s = float(utc.second) + float(utc.microsecond)*1e-6 + hours = dmsUnits.mixToSingle ( (utc.hour, utc.minute, s) ) + fracDay = hours / 24.0 + #-- 3 -- + y = utc.year + m = utc.month + d = utc.day + #-- 4 -- + if m <= 2: + y, m = y-1, m+12 + #-- 5 -- + if ( (y, m, d) >= (1582, 10, 15) ): + A = int ( y / 100 ) + B = 2 - A + int ( A / 4 ) + else: + B = 0 + #-- 6 -- + C = int ( 365.25 * y ) + D = int ( 30.6001 * ( m + 1 ) ) + #-- 7 -- + # [ if fracDay+0.5 >= 1.0 -> + # s += 1 + # fracDay := (fracDay+0.5) % 1.0 + # else -> + # fracDay := fracDay + 0.5 ] + dayCarry, fracDay = divmod ( fracDay+0.5, 1.0 ) + d += dayCarry + + #-- 8 -- + j = B + C + D + d + 1720994 + + #-- 9 -- + return JulianDate ( j, fracDay ) + fromDatetime = staticmethod(fromDatetime) + +# - - - - - c l a s s S i d e r e a l T i m e + +class SiderealTime: + """Represents a sidereal time value. + + State/Internals: + .hours: [ self as 15-degree hours ] + .radians: [ self as radians ] + """ +# - - - S i d e r e a l T i m e . _ _ i n i t _ _ + + def __init__ ( self, hours ): + """Constructor for SiderealTime + """ + self.hours = hours % 24.0 + self.radians = hoursToRadians ( self.hours ) +# - - - S i d e r e a l T i m e . _ _ s t r _ _ + + def __str__ ( self ): + """Convert to a string such as "[04h 40m 5.170s]". + """ + + #-- 1 -- + # [ values := self.hours as a list of mixed units + # in dmsUnits terms, formatted as left-zero + # filled strings with 3 digits after the decimal ] + mix = dmsUnits.singleToMix ( self.hours ) + values = dmsUnits.format ( mix, decimals=3, lz=True ) + + #-- 2 -- + return "[%sh %sm %ss]" % tuple(values) +# - - - S i d e r e a l T i m e . u t c + + def utc ( self, date ): + """Convert GST to UTC. + + [ date is a UTC date as a datetime.date instance -> + return the first or only time at which self's GST + occurs at longitude 0 ] + """ + #-- 1 -- + # [ nDays := number of days between Jan. 0 of year + # (date.year) and date ] + nDays = dayNo ( date ) + #-- 2 -- + # [ t0 := (nDays * A - B(date.year)), normalized to + # interval [0,24) ] + t0 = ( ( nDays * SIDEREAL_A - + SiderealTime.factorB ( date.year ) ) % 24.0 ) + #-- 3 -- + # [ t1 := ((self in decimal hours)-t0), normalized to + # the interval [0,24) ] + t1 = ( radiansToHours ( self.radians ) - t0 ) % 24.0 + #-- 4 -- + gmtHours = t1 * 0.997270 + #-- 5 -- + # [ dt := a datetime.datetime instance whose date comes + # from (date) and whose time is (gmtHours) + # decimal hours ] + hour, minute, floatSec = dmsUnits.singleToMix ( gmtHours ) + wholeSec, fracSec = divmod ( floatSec, 1.0 ) + second = int ( wholeSec ) + micros = int ( fracSec * 1e6 ) + dt = datetime.datetime ( date.year, date.month, + date.day, hour, minute, second, micros ) + + #-- 6 -- + return dt +# - - - S i d e r e a l T i m e . f a c t o r B + +# @staticmethod + def factorB ( yyyy ): + """Compute sidereal conversion factor B for a given year. + + [ yyyy is a year number as an int -> + return the GST at time yyyy-01-00T00:00 ] + """ + #-- 1 -- + # [ janJD := the Julian date of January 0.0 of year + # (yyyy), as a float ] + janDT = datetime.datetime ( yyyy, 1, 1 ) + janJD = float(JulianDate.fromDatetime(janDT)) - 1.0 + #-- 2 -- + s = janJD - 2415020.0 + + #-- 3 -- + t = s / 36525.0 + + #-- 4 -- + r = ( 0.00002581 * t + + 2400.051262 ) * t + 6.6460656 + #-- 5 -- + u = r - 24 * ( yyyy-1900) + + #-- 6 -- + return 24.0 - u + + factorB = staticmethod(factorB) +# - - - S i d e r e a l T i m e . g s t + + def gst ( self, eLong ): + """Convert LST to GST. + + [ self is local sidereal time at longitude eLong + radians east of Greenwich -> + return the equivalent GST as a SiderealTime instance ] + """ + #-- 1 -- + # [ deltaHours := eLong expressed in hours ] + deltaHours = radiansToHours ( eLong ) + + #-- 2 -- + gstHours = ( self.hours - deltaHours ) % 24.0 + + #-- 3 -- + return SiderealTime ( gstHours ) +# - - - S i d e r e a l T i m e . l s t + + def lst ( self, eLong ): + """Convert GST to LST. + + [ (self is Greenwich sidereal time) and + (eLong is a longitude east of Greenwich in radians) -> + return a new SiderealTime representing the LST + at longitude eLong ] + """ + #-- 1 -- + # [ deltaHours := eLong expressed in hours ] + deltaHours = radiansToHours ( eLong ) + + #-- 2 -- + gmtHours = (self.hours + deltaHours) % 24.0 + + #-- 3 -- + return SiderealTime ( gmtHours ) +# - - - S i d e r e a l T i m e . f r o m D a t e t i m e + + SIDEREAL_C = 1.002738 + +# @staticmethod + def fromDatetime ( dt ): + """Convert civil time to Greenwich Sidereal. + + [ dt is a datetime.datetime instance -> + if dt has time zone information -> + return the GST at the UTC equivalent to dt + else -> + return the GST assuming dt is UTC ] + """ + #-- 1 -- + # [ if dt is naive -> + # utc := dt + # else -> + # utc := the UTC time equivalent to dt ] + utc = dt + tz = dt.tzinfo + if tz is not None: + offset = tz.utcoffset ( dt ) + if offset is not None: + utc = dt - offset + #-- 2 -- + # [ nDays := number of days between January 0.0 and utc ] + nDays = dayNo ( utc ) + #-- 3 -- + t0 = ( nDays * SIDEREAL_A - + SiderealTime.factorB ( utc.year ) ) + #-- 4 -- + # [ decUTC := utc as decimal hours ] + floatSec = utc.second + float ( utc.microsecond ) / 1e6 + decUTC = dmsUnits.mixToSingle ( + (utc.hour, utc.minute, floatSec) ) + #-- 4 -- + # [ gst := (decUTC * C + t0), normalized to interval [0,24) ] + gst = ( decUTC * SiderealTime.SIDEREAL_C + t0) % 24.0 + + #-- 5 -- + return SiderealTime ( gst ) + fromDatetime = staticmethod ( fromDatetime ) + +# - - - - - c l a s s A l t A z + +class AltAz: + """Represents a sky location in horizon coords. (altitude/azimuth) + + Exports/Invariants: + .alt: [ altitude in radians, in [-pi,+pi] ] + .az: [ azimuth in radians, in [0,2*pi] ] + """ +# - - - A l t A z . _ _ i n i t _ _ + + def __init__ ( self, alt, az ): + """Constructor for AltAz, horizon coordinates. + + [ (alt is an altitude in radians) and + (az is an azimuth in radians) -> + return a new AltAz instance with those values, + normalized as per class invariants ] + """ + self.alt = alt + self.az = az +# - - - A l t A z . r a D e c + + def raDec ( self, lst, latLon ): + """Convert horizon coordinates to equatorial. + + [ (lst is a local sidereal time as a SiderealTime instance) and + (latLon is the observer's position as a LatLon instance) -> + return the corresponding equatorial coordinates as a + RADec instance ] + """ + #-- 1 -- + # [ dec := declination of self at latLon in radians + # hourRadians := hour angle of self at latlon in radians ] + dec, hourRadians = coordRotate ( self.alt, latLon.lat, + self.az ) + + #-- 2 -- + # [ hourRadians is an hour angle in radians -> + # h := hourRadians in hours ] + h = radiansToHours ( hourRadians ) + #-- 3 -- + # [ ra := right ascension for hour angle (h) at local + # sidereal time (lst) and location (latLon) ] + ra = hoursToRadians ( ( lst.hours - h ) % 24.0 ) + + #-- 4 -- + return RADec ( ra, dec ) +# - - - A l t A z . _ _ s t r _ _ + + def __str__ ( self ): + """Convert self to a string. + """ + #-- 1 -- + # [ altList := self.alt, formatted as degrees, minutes, + # and seconds + # azList := self.az, formatted as degrees, minutes, and + # seconds ] + altList = dmsUnits.format ( dmsUnits.singleToMix ( + degrees(self.alt) ), lz=True, decimals=3 ) + azList = dmsUnits.format ( dmsUnits.singleToMix ( + degrees(self.az) ), lz=True, decimals=3 ) + + #-- 2 -- + return ( "[az %sd %s' %s\" alt %sd %s' %s\"]" % + (tuple(azList)+tuple(altList)) ) +# - - - c o o r d R o t a t e + +def coordRotate ( x, y, z ): + """Used to convert between equatorial and horizon coordinates. + + [ x, y, and z are angles in radians -> + return (xt, yt) where + xt=arcsin(sin(x)*sin(y)+cos(x)*cos(y)*cos(z)) and + yt=arccos((sin(x)-sin(y)*sin(xt))/(cos(y)*cos(xt))) ] + """ + #-- 1 -- + xt = asin ( sin(x) * sin(y) + + cos(x) * cos(y) * cos(z) ) + #-- 2 -- + yt = acos ( ( sin(x) - sin(y) * sin(xt) ) / + ( cos(y) * cos(xt) ) ) + #-- 3 -- + if sin(z) > 0.0: + yt = TWO_PI - yt + + #-- 4 -- + return (xt, yt) + +# - - - - - c l a s s R A D e c + +class RADec: + """Represents a celestial location in equatorial coordinates. + + Exports/Invariants: + .ra: [ right ascension in radians ] + .dec: [ declination in radians ] + """ +# - - - R A D e c . _ _ i n i t _ _ + + def __init__ ( self, ra, dec ): + """Constructor for RADec. + """ + self.ra = ra % TWO_PI + self.dec = dec +# - - - R A D e c . h o u r A n g l e + + def hourAngle ( self, utc, eLong ): + """Find the hour angle at a given observer's location. + + [ (utc is a Universal Time as a datetime.datetime) and + (eLong is an east longitude in radians) -> + return the hour angle of self at that time and + longitude, in radians ] + """ + return raToHourAngle ( self.ra, utc, eLong ) +# - - - R A D e c . a l t A z + + def altAz ( self, h, lat ): + """Convert equatorial to horizon coordinates. + + [ (h is an object's hour angle in radians) and + (lat is the observer's latitude in radians) -> + return self's position in the observer's sky + in horizon coordinates as an AltAz instance ] + """ + #-- 1 -- + # [ alt := altitude of self as seen from latLon at utc + # az := azimuth of self as seen from latLon at utc ] + alt, az = coordRotate ( self.dec, lat, h ) + + #-- 2 -- + return AltAz ( alt, az ) +# - - - R A D e c . _ _ s t r _ _ + + def __str__ ( self ): + """Return self as a string. + """ + #-- 1 -- + # [ raUnits := units of self.ra as hours/minutes/seconds + # decUnits := units of self.dec as degrees/minutes/seconds + raUnits = dmsUnits.format ( + dmsUnits.singleToMix ( radiansToHours(self.ra) ), + lz=True, decimals=3 ) + decUnits = dmsUnits.format ( + dmsUnits.singleToMix ( degrees(self.dec) ), + lz=True, decimals=3 ) + + #-- 2 -- + return ( "[%sh %sm %ss, %sd %s' %s\"]" % + (tuple(raUnits)+tuple(decUnits)) ) diff --git a/ppf/db-mysql/status_messages.sample.txt b/ppf/db-mysql/status_messages.sample.txt new file mode 100644 index 0000000..3e0526c --- /dev/null +++ b/ppf/db-mysql/status_messages.sample.txt @@ -0,0 +1,8 @@ +1, 2009-10-30 20:12:32, 23, 1, 324, 43, 121323, 12132, 14m12s +1, 2009-10-30 20:12:33, 23, 1, 324, 43, 122323, 12232, 14m12s +1, 2009-10-30 20:12:34, 23, 1, 323, 43, 123323, 12332, 14m12s +1, 2009-10-30 20:12:35, 24, 2, 322, 44, 124323, 12432, 14m12s +1, 2009-10-30 20:12:36, 24, 3, 320, 45, 125323, 12532, 14m12s +1, 2009-10-30 20:12:37, 25, 3, 319, 49, 126323, 12632, 14m12s +1, 2009-10-30 20:12:38, 26, 2, 306, 48, 127323, 12732, 14m12s +1, 2009-10-30 20:12:39, 25, 2, 301, 48, 128323, 12932, 14m12s diff --git a/ppf/db-mysql/swarms.sample.txt b/ppf/db-mysql/swarms.sample.txt new file mode 100644 index 0000000..b0158eb --- /dev/null +++ b/ppf/db-mysql/swarms.sample.txt @@ -0,0 +1 @@ +simple-iso-file, 731906048, limit-speed-experiment, local diff --git a/ppf/db-mysql/verbose_messages.sample.txt b/ppf/db-mysql/verbose_messages.sample.txt new file mode 100644 index 0000000..a11219d --- /dev/null +++ b/ppf/db-mysql/verbose_messages.sample.txt @@ -0,0 +1,8 @@ +1, 2009-10-30 12:32:34.22, 10.0.1.2, 5678, 1, 12, 16, 1024, 0 +1, 2009-10-30 12:32:34.34, 10.0.1.2, 5678, 2, 12, 16, 1024, 0 +1, 2009-10-30 12:32:35.12, 10.0.1.2, 5678, 3, 12, 16, 1024, 0 +1, 2009-10-30 12:32:35.28, 10.0.1.2, 5678, 2, 12, 16, 1024, 0 +1, 2009-10-30 12:32:35.29, 10.0.1.2, 5678, 4, 12, 16, 1024, 0 +1, 2009-10-30 12:32:36.12, 10.0.1.2, 5678, 5, 12, 16, 1024, 0 +1, 2009-10-30 12:32:37.32, 10.0.1.2, 5678, 3, 12, 16, 1024, 0 +1, 2009-10-30 12:32:37.85, 10.0.1.2, 5678, 2, 12, 16, 1024, 0 diff --git a/ppf/log-parser-mysql/generic/GenericStatusParser.py b/ppf/log-parser-mysql/generic/GenericStatusParser.py new file mode 100644 index 0000000..67b82b0 --- /dev/null +++ b/ppf/log-parser-mysql/generic/GenericStatusParser.py @@ -0,0 +1,137 @@ +#!/usr/bin/env python + +import sys +import os +import re +import datetime +import logging + +# configure logging (change to logging.ERROR when no DEBUG required) +logging.basicConfig(level=logging.ERROR) + +class GenericStatusParser: + """ + Abstract-like parser class used for parsing BitTorrent log messages. + Inherited by client-specific classes + """ + + def __init__(self, filename): + self.filename = filename + + # return boolean + # + def is_status_line(self, line): + return True + + # return integer + # + def canon_num_peers(self, non_canon_value): + return 0 + + # return integer + # + def canon_dht(self, non_canon_value): + return 0 + + # return integer + # + # 119.51kb/s -> 119 + def canon_download_speed(self, non_canon_value): + return 0 + + # return integer + # + # 12119.51kb/s -> 12119 + def canon_upload_speed(self, non_canon_value): + return 0 + + # return timedelta object + # + # 698mb -> 698*1024*1024 + def canon_download_size(self, non_canon_value): + return 0 + + # return timedelta object + # + # 492mb -> 492*1024*1024 + def canon_upload_size(self, non_canon_value): + return 0 + + # return timedelta object + # + # 1h 38m 37s -> [0, 1, 38, 37] + # 3d 5h 24m 34s -> [3, 5, 24, 34] + def canon_eta(self, non_canon_value): + return datetime.timedelta() + + def timedelta_to_seconds(self, delta): + return delta.days * 24 * 3600 + delta.seconds + + # return list of required information + def parse_status_line(self, line): + num_peers = 0 + dht = 0 + download_speed = 0 + upload_speed = 0 + download_size = 0 + upload_size = 0 + eta = 0 + timestamp = datetime.datetime(2010, 04, 30) + + return (timestamp, num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta) + + def parse_last_status_line(self, line): + try: + # read last line from status log file + f = open(self.filename, "r") + f.seek(-2, os.SEEK_END) + + # seek before the beginning of the last line + + while f.read(1) != '\n' : + f.seek(-2, os.SEEK_CUR) + line = f.readline() + print line + f.close() + except Exception, e: + print e + return [] + return self.parse_status_line(line) + + def cb_print(self, timestamp, num_peers, dht, + download_speed, upload_speed, + download_size, upload_size, + eta_seconds): + print "time = %s, ps = %d, dht = %d, ds = %d kb/s, us = %d kb/s, dsize = %d bytes, usize = %d bytes" % (timestamp.strftime("%H:%M:%S %d-%m-%y"), + num_peers, dht, download_speed, upload_speed, download_size, upload_size) + + def parse(self, callback_func, callback_arg = None): + try: + fin = open(self.filename, "r") + while 1: + line = fin.readline() + if not line: + break + + line = line.strip() + if self.is_status_line(line) == False: + continue + + (timestamp, num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta_seconds) = self.parse_status_line(line) + + logging.debug("(%d, %d, %d kb/s, %d kb/s, %d bytes, %d bytes)" % (num_peers, dht, download_speed, upload_speed, download_size, upload_size)) + + if callback_arg == None: + callback_func(timestamp, num_peers, dht, + download_speed, upload_speed, + download_size, upload_size, + eta_seconds) + else: + callback_func(callback_arg, timestamp, + num_peers, dht, + download_speed, upload_speed, + download_size, upload_size, + eta_seconds) + + except IOError: + logging.error("Error processing file %s." % self.filename) diff --git a/ppf/log-parser-mysql/generic/LibtorrentStatusParser.py b/ppf/log-parser-mysql/generic/LibtorrentStatusParser.py new file mode 100644 index 0000000..429b726 --- /dev/null +++ b/ppf/log-parser-mysql/generic/LibtorrentStatusParser.py @@ -0,0 +1,145 @@ +#!/usr/bin/env python + +import sys +import re +import datetime +import logging + +from GenericStatusParser import GenericStatusParser + +# configure logging (change to logging.ERROR when no DEBUG required) +logging.basicConfig(level=logging.ERROR) + +ONE_SECOND = datetime.timedelta(0, 1) + +class LibtorrentStatusParser(GenericStatusParser): + """ + Abstract-like parser class used for parsing BitTorrent log messages. + Inherited by client-specific classes + """ + + def __init__(self, filename, start_time): + GenericStatusParser.__init__(self, filename) + self.start_time = start_time + self.timestamp = start_time + + # return boolean + # + def is_status_line(self, line): + if re.match("^ps", line) == None: + return False + return True + + # return integer + # + def canon_num_peers(self, non_canon_value): + return int(non_canon_value) + + # return integer + # + def canon_dht(self, non_canon_value): + return int(non_canon_value) + + # return datetime object + # + def canon_datetime(self, non_canon_value): + string_parts = re.split("\ *", non_canon_value) + if len(string_parts) != 2: + return None + date_array = string_parts[0].split("-"); + time_array = string_parts[1].split(":"); + if len(date_array) != 3 or len(time_array) != 3: + return None + timestamp = datetime.datetime(int(date_array[2]), int(date_array[1]), int(date_array[0]), #year, month, day + int(time_array[0]), int(time_array[1]), int(time_array[2])) #hour, min, sec + return timestamp + + # return integer + # + # 119.51kb/s -> 119 + def canon_download_speed(self, non_canon_value): + return int(float(non_canon_value.strip("kb/s"))) + + # return integer + # + # 12119.51kb/s -> 12119 + def canon_upload_speed(self, non_canon_value): + return int(float(non_canon_value.strip("kb/s"))) + + # return timedelta object + # + # 698mb -> 698*1024*1024 + def canon_download_size(self, non_canon_value): + return int(non_canon_value.strip("mb")) * 1024 * 1024 + + # return timedelta object + # + # 492mb -> 492*1024*1024 + def canon_upload_size(self, non_canon_value): + return int(non_canon_value.strip("mb")) * 1024 * 1024 + + # return timedelta object + # + # 1h 38m 37s -> [0, 1, 38, 37] + # 3d 5h 24m 34s -> [3, 5, 24, 34] + def canon_eta(self, non_canon_value): + eta_string_array = re.split('\ *[dhms]\ *', non_canon_value) + eta_string_array.remove('') + eta = [] + for i in range(0, len(eta_string_array)): + eta.append(int(eta_string_array[i])) + for i in range(len(eta_string_array), 4): + eta.insert(0, 0) + + return datetime.timedelta(eta[0], eta[3], 0, 0, eta[2], eta[1], 0) + + # return list of required information + def parse_status_line(self, line): + num_peers = 0 + dht = 0 + datetime = 0 + download_speed = 0 + upload_speed = 0 + download_size = 0 + upload_size = 0 + eta = 0 + + string_array = re.split("\ *[,<>]+\ *", line) + logging.debug("string_array is %s" % string_array) + + for string in string_array: + pair = re.split("\ *:\ +", string) + if pair[0] == "ps": + num_peers = self.canon_num_peers(pair[1]) + if pair[0] == "dht": + dht = self.canon_dht(pair[1]) + if pair[0] == "time": + datetime = self.canon_datetime(pair[1]) + if pair[0] == "dl": + download_speed = self.canon_download_speed(pair[1]) + if pair[0] == "ul": + upload_speed = self.canon_upload_speed(pair[1]) + if pair[0] == "dld": + download_size = self.canon_download_size(pair[1]) + if pair[0] == "uld": + upload_size = self.canon_upload_size(pair[1]) + if pair[0] == "size": + pass + if pair[0] == "eta": + eta_seconds = self.timedelta_to_seconds(self.canon_eta(pair[1])) + + self.timestamp += ONE_SECOND + self.timestamp = datetime + return (self.timestamp, num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta_seconds) + + +def main(): + if len(sys.argv) != 2: + print "Usage: %s filename" % (sys.argv[0]) + sys.exit(1) + + sp = LibtorrentStatusParser(sys.argv[1], datetime.datetime(2010, 04, 30)) + sp.parse(sp.cb_print) + +if __name__ == "__main__": + sys.exit(main()) diff --git a/ppf/log-parser-mysql/generic/TriblerStatusParser.py b/ppf/log-parser-mysql/generic/TriblerStatusParser.py new file mode 100644 index 0000000..a39a0d0 --- /dev/null +++ b/ppf/log-parser-mysql/generic/TriblerStatusParser.py @@ -0,0 +1,166 @@ +#!/usr/bin/env python + +import sys +import re +import datetime +import logging + +from GenericStatusParser import GenericStatusParser + +class TriblerStatusParser(GenericStatusParser): + """ + Parser class used for parsing Tribler BitTorrent log messages. + @author Adriana Draghici + """ + + def __init__(self, filename): + GenericStatusParser.__init__(self, filename) + self.filesize = self.get_file_size() + + def is_status_line(self, line): + """ Check if status line. All status messages contain a + status string (e.g. DLSTATUS_DOWNLOADING). + @return boolean + """ + if line.find("DLSTATUS_DOWNLOADING") > -1 or line.find("DLSTATUS_SEEDING") > -1: + return True + return False + + def is_single_download_line(self, line): + """ Tribler's SingleDownload lines contain information about the + torrent file. + @return boolean + """ + if line.find("SingleDownload") == -1: + return False + return True + + def get_file_size(self): + # """ Parse a line with this format: + # SingleDownload: save_as( u'' '' ) + # Saves the file name and size. If the line does not correspond to this format, it does nothing. + + try: + fin = open(self.filename, "r") + while 1: + line = fin.readline() + if not line: + break + + line = line.strip() + if self.is_single_download_line(line) == True: + if line.find("save_as") != -1: + parts = line.split("'") + return int(parts[2]) + break + fin.close() + except IOError: + logger.error("Error processing file %s." % (self.filename)) + return -1 + + return 0 + + def canon_num_peers(self, non_canon_value): + """ @return integer """ + return int(non_canon_value) + + def canon_dht(self, non_canon_value): + """ @return integer """ + return int(non_canon_value) + + def canon_download_speed(self, non_canon_value): + """@return integer, eg. 119.51kb/s -> 119 """ + return int(float(non_canon_value.strip("KB/s"))) + + def canon_upload_speed(self, non_canon_value): + """@return integer, eg. 12119.51kb/s -> 12119 """ + return int(float(non_canon_value.strip("KB/s"))) + + def canon_download_size(self, non_canon_value): + """@return integer, eg. 25% -> 25*file_size/100""" + return int(float(non_canon_value.strip("%")) * self.filesize / 100) + + def canon_upload_size(self, non_canon_value): + return 0 + + def canon_eta(self, non_canon_value): + """@return integer, eg. 26.456787 -> 26 (seconds)""" + if non_canon_value != 'None': + return int(float(non_canon_value)) + return None + + def parse_timestamp(self, date, time): + """ Get date and timestamp and transform it into datetime format. + Format: dd-mm-yyyy hh:mm:ss + @return datetime object + """ + + date_array = date.split("-"); + time_array = time.split(":"); + if len(date_array) != 3 or len(time_array) != 3: + return None + + timestamp = datetime.datetime(int(date_array[2]), int(date_array[1]), int(date_array[0]), #year, month, day + int(time_array[0]), int(time_array[1]), int(time_array[2])) #hour, min, sec + return timestamp + + # return list of required + def parse_status_line(self, line): + # + # sample tribler status line + # 03-Nov-2009 12:18:55 aqua.mpeg DLSTATUS_DOWNLOADING 29.84% None up 0.00KB/s down 4414.39KB/s eta 12 peers 2 + # + num_peers = 0 + dht = 0 + download_speed = 0 + upload_speed = 0 + download_size = 0 + upload_size = 0 + eta = 0 + timestamp = None + string_array = re.split("\ *", line) + + logging.debug("string_array is: " + str(string_array)) + if len(string_array) != 14: + logging.error("Invalid line format!") + return None + + # get timestamp and transform it in datetime format + timestamp= self.parse_timestamp(string_array[0], string_array[1]) + + i = 3 + while i < len(string_array): #string_array: + if string_array[i] == "peers": + num_peers = self.canon_num_peers(string_array[i+1]) + i = i + 2 + continue + if string_array[i] == "down": + download_speed = self.canon_download_speed(string_array[i+1]) + i = i + 2 + continue + if string_array[i] == "up": + upload_speed = self.canon_upload_speed(string_array[i+1]) + i = i + 2 + continue + if string_array[i] == "DLSTATUS_DOWNLOADING" or string_array[i] == "DLSTATUS_SEEDING": + download_size = self.canon_download_size(string_array[i+1]) + i = i + 2 + continue + if string_array[i] == "eta": + eta = self.canon_eta(string_array[i+1]) + i = i + 2 + continue + i = i + 1 + return (timestamp, num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta) + + +def main(): + if len(sys.argv) != 2: + print "Usage: %s filename" % (sys.argv[0]) + sys.exit(1) + + sp = TriblerStatusParser(sys.argv[1]) + sp.parse(sp.cb_print) + +if __name__ == "__main__": + sys.exit(main()) diff --git a/ppf/log-parser-mysql/generic/libtorrent_parser_test b/ppf/log-parser-mysql/generic/libtorrent_parser_test new file mode 100755 index 0000000..1b06303 --- /dev/null +++ b/ppf/log-parser-mysql/generic/libtorrent_parser_test @@ -0,0 +1,6 @@ +#!/bin/bash + +sample=../../log-samples/libtorrent/libtorrent-status.sample.log + +#PYTHONPATH=../../db/ python LibtorrentStatusParser.py "$sample" +python LibtorrentStatusParser.py "$sample" diff --git a/ppf/log-parser-mysql/generic/tribler_parser_test b/ppf/log-parser-mysql/generic/tribler_parser_test new file mode 100755 index 0000000..f132501 --- /dev/null +++ b/ppf/log-parser-mysql/generic/tribler_parser_test @@ -0,0 +1,5 @@ +#!/bin/bash + +sample=../../log-samples/tribler/status_sample.out + +PYTHONPATH=../../db/ python TriblerStatusParser.py "$sample" diff --git a/ppf/log-parser-mysql/libtorrent/LogParser.py b/ppf/log-parser-mysql/libtorrent/LogParser.py new file mode 100644 index 0000000..f6417c6 --- /dev/null +++ b/ppf/log-parser-mysql/libtorrent/LogParser.py @@ -0,0 +1,570 @@ +#!/usr/bin/env python + +# +# Parser for libtorrent-rasterbar verbose messages +# http://www.rasterbar.com/products/libtorrent/ +# +# author: 2009, Adriana Draghici, adriana008@gmail.com +# updates: November 2009, Razvan Deaconescu, razvan.deaconescu@cs.pub.ro +# + +import sys +from DatabaseWriter import DatabaseWriter +from DatabaseCommander import DatabaseCommander +import julian +import datetime +import time +import getopt +import re +import socket +import string +import os + +# the names used by Tribler for the BitTorrent messages +bt_msg_types = {"CHOKE": 0, "UNCHOKE": 1, "INTERESTED": 2, + "NOT_INTERESTED": 3, "HAVE": 4, "BITFIELD": 5, + "REQUEST": 6, "PIECE": 7, "CANCEL": 8, "DHT_PORT": 9} + +log_msg_dir = {"RECEIVE": 0, "SEND": 1} + +DEBUG = False + +LOG_YEAR = 2009 + +# +# convert string "Mon DD HH:MM:SS" to datetime +# + +def string_to_timestamp(date_string): + try: + my_time = time.strptime(date_string + " %s" % (LOG_YEAR), "%b %d %H:%M:%S %Y") + my_date = datetime.datetime(my_time[0], my_time[1], my_time[2], my_time[3], my_time[4], my_time[5], my_time[6]) + except ValueError: + print "Invalid date:", date_string + + return my_date + +# +# parse choke line in libtorrent log file +# +# sample line +# Jan 08 22:39:50 <== CHOKE +# + +def libtorrent_parse_choke(line): + if string.find(line, "<== CHOKE") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> CHOKE") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match CHOKE" + + msg_type = bt_msg_types["CHOKE"] + parts = re.split("[<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = 0 + begin = 0 + length = 0 + port = 0 + + return (timestamp, direction, msg_type, index, begin, length, port) + +# +# parse unchoke line in libtorrent log file +# +# sample line +# Jan 08 22:40:00 <== UNCHOKE + +def libtorrent_parse_unchoke(line): + if string.find(line, "<== UNCHOKE") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> UNCHOKE") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match UNCHOKE" + + msg_type = bt_msg_types["UNCHOKE"] + parts = re.split("[<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = 0 + begin = 0 + length = 0 + port = 0 + + return (timestamp, direction, msg_type, index, begin, length, port) + +# +# parse interested line in libtorrent log file +# +# sample line +# Jan 08 22:20:48 ==> INTERESTED +# + +def libtorrent_parse_interested(line): + if string.find(line, "<== INTERESTED") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> INTERESTED") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match INTERESTED" + + msg_type = bt_msg_types["INTERESTED"] + parts = re.split("[<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = 0 + begin = 0 + length = 0 + port = 0 + + return (timestamp, direction, msg_type, index, begin, length, port) + +# +# parse not interested line in libtorrent log file +# +# sample line +# Jan 08 22:39:49 ==> NOT_INTERESTED +# + +def libtorrent_parse_not_interested(line): + if string.find(line, "<== NOT_INTERESTED") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> NOT_INTERESTED") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match NOT_INTERESTED" + + msg_type = bt_msg_types["NOT_INTERESTED"] + parts = re.split("[<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = 0 + begin = 0 + length = 0 + port = 0 + + return (timestamp, direction, msg_type, index, begin, length, port) + +# +# parse have line in libtorrent log file +# +# sample line +# Jan 08 22:20:48 <== HAVE [ piece: 839] +# + +def libtorrent_parse_have(line): + if string.find(line, "<== HAVE ") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> HAVE ") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match HAVE" + + msg_type = bt_msg_types["HAVE"] + parts = re.split("[\[\]<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = int("0x" + re.split(":", parts[2].strip())[1].strip(), 16) + begin = 0 + length = 0 + port = 0 + + return (timestamp, direction, msg_type, index, begin, length, port) + +# +# parse bitfield line in libtorrent log file +# +# sample line +# Jan 08 22:20:48 ==> BITFIELD 00000... +# + +def libtorrent_parse_bitfield(line): + if string.find(line, "<== BITFIELD ") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> BITFIELD ") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match BITFIELD" + + msg_type = bt_msg_types["BITFIELD"] + parts = re.split("[<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = 0 + begin = 0 + length = 0 + port = 0 + + return (timestamp, direction, msg_type, index, begin, length, port) + +# +# parse request line in libtorrent log file +# +# sample line +# Jan 08 22:39:50 <== REQUEST [ piece: 6cc | s: 14000 | l: 4000 ] +# + +def libtorrent_parse_request(line): + if string.find(line, "<== REQUEST ") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> REQUEST ") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match REQUEST" + + msg_type = bt_msg_types["REQUEST"] + parts = re.split("[\[\]|<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = int("0x" + re.split(":", parts[2])[1].strip(), 16) + begin = int("0x" + re.split(":", parts[3])[1].strip(), 16) + length = int("0x" + re.split(":", parts[4])[1].strip(), 16) + + port = 0 + + return (timestamp, direction, msg_type, index, begin, length, port) + +# +# parse piece line in libtorrent log file +# +# sample line +# Jan 08 22:39:50 ==> PIECE [ piece: 5c6 | s: 24000 | l: 4000 ] +# + +def libtorrent_parse_piece(line): + if string.find(line, "<== PIECE ") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> PIECE ") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match PIECE" + + msg_type = bt_msg_types["PIECE"] + parts = re.split("[\[\]|<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = int("0x" + re.split(":", parts[2])[1].strip(), 16) + begin = int("0x" + re.split(":", parts[3])[1].strip(), 16) + length = int("0x" + re.split(":", parts[4])[1].strip(), 16) + port = 0 + + return (timestamp, direction, msg_type, index, begin, length, port) + +# +# no cancel line in libtorrent log files +# + +def libtorrent_parse_cancel(line): + return None + +# +# parse allowed fast line in libtorrent log file +# +# sample line +# Jan 08 22:20:48 ==> ALLOWED_FAST [ 2098 ] +# + +def libtorrent_parse_allowed_fast(line): + if string.find(line, "<== ALLOWED_FAST ") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> ALLOWED_FAST ") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match ALLOWED_FAST" + + msg_type = bt_msg_types["ALLOWED_FAST"] + parts = re.split("[\[\]<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = 0 + begin = 0 + length = 0 + port = int(parts[2].strip()) + + return (timestamp, direction, msg_type, index, begin, length, port) + +# +# parse DHT port line in libtorrent log file +# +# sample line +# Jan 08 22:20:48 ==> DHT_PORT [ 50200 ] +# + +def libtorrent_parse_port(line): + if string.find(line, "<== DHT_PORT ") != -1: + direction = log_msg_dir["RECEIVE"] + elif string.find(line, "==> DHT_PORT ") != -1: + direction = log_msg_dir["SEND"] + else: + return None + + if DEBUG == True: + print "--- match DHT_PORT" + + msg_type = bt_msg_types["DHT_PORT"] + parts = re.split("[\[\]<=>]+", line) + + timestamp = string_to_timestamp(parts[0].strip()) + index = 0 + begin = 0 + length = 0 + if direction == log_msg_dir["RECEIVE"]: + port = int("0x" + re.split(":", parts[2])[1].strip(), 16) + else: + port = int(parts[2].strip()) + + return (timestamp, direction, msg_type, index, begin, length, port) + + +# +# parse libtorrent-rasterbar log file line +# +# @line: libtorrent parse log file +# + +def libtorrent_parse_log_line(line): + + result = libtorrent_parse_choke(line) + if result != None: + return result + + result = libtorrent_parse_unchoke(line) + if result != None: + return result + + result = libtorrent_parse_interested(line) + if result != None: + return result + + result = libtorrent_parse_not_interested(line) + if result != None: + return result + + result = libtorrent_parse_have(line) + if result != None: + return result + + result = libtorrent_parse_bitfield(line) + if result != None: + return result + + result = libtorrent_parse_request(line) + if result != None: + return result + + result = libtorrent_parse_piece(line) + if result != None: + return result + + result = libtorrent_parse_cancel(line) + if result != None: + return result + + result = libtorrent_parse_port(line) + if result != None: + return result + + +# +# parse libtorrent-rasterbar log file +# +# @dbw - DatabaseWriter instance +# @client_session_id - client session id in swarm +# @logfile - log file +# + +def libtorrent_parse_log_file(dbw, client_session_id, logfile): + + if os.path.exists(logfile) == False: + print "No such file:", logfile + + basename = os.path.basename(logfile) + + # file name has to follow the ${IP_ADDRESS}_${PORT}.log syntax + tmp_parts = re.split("_", basename) + peer_ip = tmp_parts[0] + tmp_parts2 = re.split("\.", tmp_parts[1]) + str_peer_port = tmp_parts2[0] + extension = tmp_parts2[1] + + try: + socket.inet_aton(peer_ip) + except socket.error: + print "Invalid IP address:", peer_ip + return + + try: + peer_port = int(str_peer_port) + except TypeError: + print "Invalid port:", str_peer_port + return + + if extension != "log": + print "Invalid file name: ", basename + return + + try: + fin = open(logfile, "r") + while 1: + line = fin.readline() + if not line: + break + + line = line.strip() + + if DEBUG == True: + print "+++", line + + result = libtorrent_parse_log_line(line) + if result == None: + continue + + (timestamp, direction, msg_type, index, begin, length, listen_port) = result + if DEBUG == True: + print result + + dbw.add_verbose_message_datetime(client_session_id, timestamp, + direction, peer_ip, peer_port, msg_type, + index, begin,length, listen_port) + + except IOError: + print "Error processing file %s." %logfile + +def usage(): + print "Usage: python StatusParser.py -i|--id id -f|--file log_file database" + print "id:" + print "\t--id" + print "\t-i\t\tclient_session_id" + print "\tstatus_file:" + print "\t--file" + print "\t-f\t\tstatus_file for tribler" + print "\tdatabase\t\tSQLite database file" + print "\t--help" + print "\t-h\t\t\tprint this help screen" + + +def main_just_parse(): + filename = sys.argv[1] + client_session_id = 1 + tribler_parse_status_file(None, 1, filename) + + +def main_with_DB(): + + try: + opts, args = getopt.getopt(sys.argv[1:], "hi:f:", ["help", + "id=", "file="]) + except getopt.GetoptError, err: + print str(err) + usage() + sys.exit(2) + + client_session_id = None + filename = None + database = None + + for o, a in opts: + if o in ("-h", "--help"): + usage() + sys.exit(0) + elif o in ("-i", "--id"): + client_session_id = int(a) + elif o in ("-f", "--file"): + filename = a + else: + assert False, "unhandled option" + + if client_session_id == None: + print "Error: no client session id." + sys.exit(2) + + if filename == None: + print "Error: no status file." + sys.exit(2) + + # no database passed as argument + if len(args) != 1: + print "Error: no database file passed as argument." + sys.exit(2) + database = args[0] + + dbc = DatabaseCommander(database) + + # check for client_session_id, swarm_id, btclient_id + cursor = dbc.select_client_sessions_by_id(client_session_id) + if cursor == None: + print "Error: no client session id (%d) in database." % client_session_id + sys.exit(2) + for session_row in cursor: + pass + + swarm_id = session_row[1] + btclient_id = session_row[2] + + cursor = dbc.select_swarms(swarm_id) + if cursor == None: + print "Error: no swarm id (%d) in database." % swarm_id + sys.exit(2) + for swarm_row in cursor: + pass + + cursor = dbc.select_btclients(btclient_id) + if cursor == None: + print "Error: no client id (%d) in database." % btclient_id + sys.exit(2) + for btclient_row in cursor: + pass + + print "Client session row is: " + print " ", session_row + print "Swarm row is: " + print " ", swarm_row + print "Client row is: " + print " ", btclient_row + print "\nContinue parsing on file %s? (y/n) " % filename, + try: + ans = sys.stdin.readline().strip() + if ans != "y": + sys.exit(0) + except IOError: + print "Error reading standard input." + sys.exit(2) + print "" + + # parse log file + dbw = DatabaseWriter(database) + libtorrent_parse_log_file(dbw, client_session_id, filename) + + +if __name__ == "__main__": + sys.exit(main_with_DB()) + #sys.exit(main_just_parse()) diff --git a/ppf/log-parser-mysql/libtorrent/README b/ppf/log-parser-mysql/libtorrent/README new file mode 100644 index 0000000..dc9754a --- /dev/null +++ b/ppf/log-parser-mysql/libtorrent/README @@ -0,0 +1,30 @@ +== StatusParser.py == + +libtorrent status log files parser. A status log file is passed as argument. +Lines are parsed and written to status_messages table in database. + +Its arguments are a client session id, the status filename and the +database name. + +== LogParser.py == + +libtorrent verbose log files parser. Receives a verbose log file as argument. +Basename must have ${IP_ADDRESS}_${LISTEN_PORT}.log syntax. + +The parser recognizes the message types as defined in the BitTorrent +specification[1]. + +== log_parser == + +Verbose log archive bash script parser. Receives a .tar.gz archive and database +as argument. + +== sample run == + +In order to see the above modules at work, use the run_sample script. Just +use a simple command + ./run_sample + +Check the script to see the calling syntax for the above modules. The sample +files are locate in ../../log-samples/libtorrent/ +[1] http://wiki.theory.org/BitTorrentSpecification diff --git a/ppf/log-parser-mysql/libtorrent/StatusParser.py b/ppf/log-parser-mysql/libtorrent/StatusParser.py new file mode 100644 index 0000000..4639d3c --- /dev/null +++ b/ppf/log-parser-mysql/libtorrent/StatusParser.py @@ -0,0 +1,224 @@ +#!/usr/bin/env python + +import sys +import getopt +import re +from DatabaseWriter import DatabaseWriter +from DatabaseCommander import DatabaseCommander +import julian +import datetime + +DEBUG = False + +def usage(): + print "Usage: python StatusParser.py -i|--id id status_file" + print "id:" + print "\t--id" + print "\t-i\t\tclient_session_id" + print "\tstatus_file:" + print "\t--file" + print "\t-f\t\tstatus_file for libtorrent" + print "\tdatabase\t\tSQLite database file" + print "\t--help" + print "\t-h\t\t\tprint this help screen" + +def libtorrent_is_status_line(line): + if re.match("^ps", line) == None: + return False + return True + +def libtorrent_canon_num_peers(non_canon_value): + return int(non_canon_value) + +def libtorrent_canon_dht(non_canon_value): + return int(non_canon_value) + +# 119.51kb/s -> 119 +def libtorrent_canon_download_speed(non_canon_value): + return int(float(non_canon_value.strip("kb/s"))) + +# 12119.51kb/s -> 12119 +def libtorrent_canon_upload_speed(non_canon_value): + return int(float(non_canon_value.strip("kb/s"))) + +# 698mb -> 698*1024*1024 +def libtorrent_canon_download_size(non_canon_value): + return int(non_canon_value.strip("mb")) * 1024 * 1024 + +# 492mb -> 492*1024*1024 +def libtorrent_canon_upload_size(non_canon_value): + return int(non_canon_value.strip("mb")) * 1024 * 1024 + +# 1h 38m 37s -> [0, 1, 38, 37] +# 3d 5h 24m 34s -> [3, 5, 24, 34] +def libtorrent_canon_eta(non_canon_value): + eta_string_array = re.split('\ *[dhms]\ *', non_canon_value) + eta_string_array.remove('') + eta = [] + for i in range(0, len(eta_string_array)): + eta.append(int(eta_string_array[i])) + for i in range(len(eta_string_array), 4): + eta.insert(0, 0) + + eta_td = datetime.timedelta(eta[0], eta[3], 0, 0, eta[2], eta[1], 0) + return eta_td + +# +# sample libtorrent status line +# ps: 1, dht: 8 <> dl: 119.51kb/s, ul: 3.63kb/s <> dld: 1mb, uld: 0mb, size: 698mb <> eta: 1h 39m 37s +# + +def libtorrent_parse_status_line(line): + num_peers = 0 + dht = 0 + download_speed = 0 + upload_speed = 0 + download_size = 0 + upload_size = 0 + eta = 0 + + string_array = re.split("\ *[,<>]+\ *", line) + if DEBUG == True: + print "string_array is: ", string_array + + for string in string_array: + pair = re.split("\ *:\ *", string) + if pair[0] == "ps": + num_peers = libtorrent_canon_num_peers(pair[1]) + if pair[0] == "dht": + dht = libtorrent_canon_dht(pair[1]) + if pair[0] == "dl": + download_speed = libtorrent_canon_download_speed(pair[1]) + if pair[0] == "ul": + upload_speed = libtorrent_canon_upload_speed(pair[1]) + if pair[0] == "dld": + download_size = libtorrent_canon_download_size(pair[1]) + if pair[0] == "uld": + upload_size = libtorrent_canon_upload_size(pair[1]) + if pair[0] == "size": + pass + if pair[0] == "eta": + eta = libtorrent_canon_eta(pair[1]) + eta_seconds = eta.days * 24 * 3600 + eta.seconds + + return (num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta_seconds) + +def libtorrent_parse_status_file(dbw, client_session_id, session_start, filename): + + message_time = session_start + one_second = datetime.timedelta(0, 1) + + try: + fin = open(filename, "r") + while 1: + line = fin.readline() + if not line: + break + + line = line.strip() + if libtorrent_is_status_line(line) == False: + continue + + (num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta_seconds) = libtorrent_parse_status_line(line) + + message_time = message_time + one_second + + if DEBUG == True: + print "(%d, %s, %s, %d, %d kb/s, %d kb/s, %d bytes, %d bytes)" % (num_peers, date, time, dht, download_speed, upload_speed, download_size, upload_size) + + dbw.add_status_message_datetime(client_session_id, message_time, num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta_seconds) + + except IOError: + print "Error processing file %s." %filename + +def main(): + try: + opts, args = getopt.getopt(sys.argv[1:], "hi:f:", ["help", + "id=", "file="]) + except getopt.GetoptError, err: + print str(err) + usage() + sys.exit(2) + + client_session_id = None + filename = None + database = None + + for o, a in opts: + if o in ("-h", "--help"): + usage() + sys.exit(0) + elif o in ("-i", "--id"): + client_session_id = int(a) + elif o in ("-f", "--file"): + filename = a + else: + assert False, "unhandled option" + + if client_session_id == None: + print "Error: no client session id." + sys.exit(2) + + if filename == None: + print "Error: no status file." + sys.exit(2) + + # no database passed as argument + if len(args) != 1: + print "Error: no database file passed as argument." + sys.exit(2) + database = args[0] + + dbc = DatabaseCommander(database) + + # check for client_session_id, swarm_id, btclient_id + cursor = dbc.select_client_sessions_by_id(client_session_id) + if cursor == None: + print "Error: no client session id (%d) in database." % client_session_id + sys.exit(2) + for session_row in cursor: + pass + + swarm_id = session_row[1] + btclient_id = session_row[2] + + cursor = dbc.select_swarms(swarm_id) + if cursor == None: + print "Error: no swarm id (%d) in database." % swarm_id + sys.exit(2) + for swarm_row in cursor: + pass + + cursor = dbc.select_btclients(btclient_id) + if cursor == None: + print "Error: no client id (%d) in database." % btclient_id + sys.exit(2) + for btclient_row in cursor: + pass + + print "Client session row is: " + print " ", session_row + print "Swarm row is: " + print " ", swarm_row + print "Client row is: " + print " ", btclient_row + + print "\nContinue parsing on file %s? (y/n) " % filename, + try: + ans = sys.stdin.readline().strip() + if ans != "y": + sys.exit(0) + except IOError: + print "Error reading standard input." + sys.exit(2) + print "" + +# session_start = julian.julianToDatetime(session_row[11]) + session_start = session_row[11] + # parse status file + dbw = DatabaseWriter(database) + libtorrent_parse_status_file(dbw, client_session_id, session_start, filename) + + +if __name__ == "__main__": + sys.exit(main()) diff --git a/ppf/log-parser-mysql/libtorrent/log_parser b/ppf/log-parser-mysql/libtorrent/log_parser new file mode 100755 index 0000000..ca269c9 --- /dev/null +++ b/ppf/log-parser-mysql/libtorrent/log_parser @@ -0,0 +1,54 @@ +#!/bin/bash + +DB_NAME=$2 +DIR_LOCAL="$(cd "$(dirname "$0")" && pwd)" +ARCHIVE=$1 + +if [ $# -lt 2 ]; then + echo -e "Usage: $0 \n" + exit 1 +fi + +# if clause to be comented before final use +if test -d temp; then + echo -e "Skipping extraction...\n" +else + +# remove temp folder in case it exists +rm -Rf temp + +# extract archive in folder ./temp +mkdir temp +echo -e "Extracting archive...\n" +tar --transform='s,^,temp/,' -xzf "$ARCHIVE" +echo -e "Archive extracted successfully.\n" +fi + +# create database +pushd $DIR_LOCAL/../../db-mysql/ +$DIR_LOCAL/../../db-mysql/db_init $DB_NAME +echo -e "Database created.\n" + +# fill database with test data +PYTHONPATH=$DIR/../../db-mysql/ python DatabaseAccess.py $DB_NAME +popd + +# fill logging information +echo -e "Parsing log files. \n" +echo $DIR_LOCAL +for folder in $( find $DIR_LOCAL/temp -type d -name 'libtorrent_logs*' | sort ); do + for log_file in $(find $folder -type f | sort); do + yes y | PYTHONPATH=$DIR_LOCAL/../../db-mysql/ python $DIR_LOCAL/LogParser.py -i 1 -f $log_file $DB_NAME + done +done +echo -e "Log files parsed.\n" + +# fill status information +echo -e "Parsing status file.\n" +PYTHONPATH=$DIR_LOCAL/../../db-mysql/ python $DIR_LOCAL/StatusParser.py -i 1 -f $DIR_LOCAL/temp/status.log $DB_NAME +echo -e "Status file parsed.\n" + +echo -e "DONE!\n" + +rm -Rf temp +exit 0 diff --git a/ppf/log-parser-mysql/libtorrent/parse_log_file b/ppf/log-parser-mysql/libtorrent/parse_log_file new file mode 100755 index 0000000..5cc13bf --- /dev/null +++ b/ppf/log-parser-mysql/libtorrent/parse_log_file @@ -0,0 +1,17 @@ +#!/bin/bash + +DB_NAME=$2 +DIR_LOCAL="$(cd "$(dirname "$0")" && pwd)" +FILE=$1 + +if [ $# -lt 2 ]; then + echo -e "Usage: $0 \n" + exit 1 +fi + +# fill logging information +yes y | PYTHONPATH=$DIR_LOCAL/../../db-mysql/ python $DIR_LOCAL/LogParser.py -i 1 -f $FILE $DB_NAME +echo -e "Log file parsed\n" +echo -e "DONE!\n" + +exit 0 diff --git a/ppf/log-parser-mysql/libtorrent/run_sample b/ppf/log-parser-mysql/libtorrent/run_sample new file mode 100755 index 0000000..b757069 --- /dev/null +++ b/ppf/log-parser-mysql/libtorrent/run_sample @@ -0,0 +1,20 @@ +#!/bin/bash + +DB_NAME=test.db + +# remove database in case it exists +rm -f $DB_NAME + +# create database +pushd . &> /dev/null +cd ../../db && ./db_init ../log-parser/libtorrent/$DB_NAME +popd &> /dev/null + +# fill database with test data +PYTHONPATH=../../db/ python ../../db/DatabaseAccess.py $DB_NAME + +# fill status information +PYTHONPATH=../../db/ python StatusParser.py -i 1 -f ../../log-samples/libtorrent/libtorrent-status.sample.log $DB_NAME + +# fill logging information +PYTHONPATH=../../db/ python LogParser.py -i 1 -f ../../log-samples/libtorrent/141.85.224.222_50200.log $DB_NAME diff --git a/ppf/log-parser-mysql/tribler/LogParser.py b/ppf/log-parser-mysql/tribler/LogParser.py new file mode 100644 index 0000000..0d6db4c --- /dev/null +++ b/ppf/log-parser-mysql/tribler/LogParser.py @@ -0,0 +1,423 @@ +#!/usr/bin/env python +# +# Parser for verbose messages +# author: Adriana Draghici < +# + +import sys +import datetime +import getopt +import julian +import logging +import re +from DatabaseWriter import DatabaseWriter +from DatabaseCommander import DatabaseCommander + +# the names used by Tribler for the BitTorrent messages +msg_types = {"BT_REQUEST_SEND": "new_request", "BT_REQUEST_RECV": "REQUEST(", "BT_CHOKE": "CHOKE from", "BT_UNCHOKE": "UNCHOKE from", + "BT_HAVE": "HAVE(", "BT_PIECE": "PIECE(", "BT_BITFIELD": "BITFIELD from", + "BT_CANCEL": "sent cancel", "BT_INTERESTED": "INTERESTED"} + +msg_db_code = {"BT_CHOKE" : 0, "BT_UNCHOKE": 1, "BT_INTERESTED": 2, "BT_NOT_INTERESTED": 3, + "BT_HAVE" : 4, "BT_BITFIELD": 5, "BT_REQUEST" : 6, "BT_PIECE": 7, "BT_CANCEL": 8} + +log_msg_dir = {"RECEIVE": 0, "SEND": 1} + +DEBUG = False + + +def usage(): + print "Usage: python StatusParser.py -i|--id id status_file" + print "id:" + print "\t--id" + print "\t-i\t\tclient_session_id" + print "\tstatus_file:" + print "\t--file" + print "\t-f\t\tstatus_file for tribler" + print "\tdatabase\t\tSQLite database file" + print "\t--help" + print "\t-h\t\t\tprint this help screen" + +""" Get date and timestamp and transform it into datetime format. + Format: dd-mm-yyyy hh:mm:ss +""" +def tribler_parse_timestamp(date, time): + + date_array = date.split("-"); + time_array = time.split(":"); + if len(date_array) != 3 or len(time_array) != 3: + return None + + timestamp = datetime.datetime(int(date_array[2]), int(date_array[1]), int(date_array[0]), #year, month, day + int(time_array[0]), int(time_array[1]), int(time_array[2])) #hour, min, sec + return timestamp + +""" + Parses a line that contains choke/unchoke messages. + line format: + date time connecter: Got CHOKE from peer_ip + date time connecter: Got UNCHOKE from peer_ip +""" +def tribler_parse_choke_msg(line): + is_choke = line.find(msg_types["BT_CHOKE"]) + is_unchoke = line.find(msg_types["BT_UNCHOKE"]) + if is_choke == -1 and is_unchoke == -1: + return None + + line_parts = re.split(" *", line) + timestamp = tribler_parse_timestamp(line_parts[0], line_parts[1]) + + if timestamp == None: + logger.error("Error: invalid date & time format for Connecter. ") + return None + + nr_parts = len(line_parts) + if nr_parts < 7 : + logger.error("Error: invalid line format for Connecter. " + line) + return None + + peer_ip = line_parts[nr_parts-1]; + direction = log_msg_dir["RECEIVE"]; + if is_choke != -1: + return (timestamp, direction, peer_ip, None, msg_db_code["BT_CHOKE"], None, None, None, 0) + + if is_unchoke != -1: + return (timestamp, direction, peer_ip, None, msg_db_code["BT_UNCHOKE"], None, None, None, 0) + +""" + Parse a line that contains interested messages. + line format: date timestamp connecter: Got INTERESTED from ip +""" + +def tribler_parse_interested_msg(line): + + if line.find(msg_types["BT_INTERESTED"]) == -1: + return None + + line_parts = re.split(" *", line) + timestamp = tribler_parse_timestamp(line_parts[0], line_parts[1]) + + if timestamp == None: + logger.error("Error: invalid date & time format for Connecter.") + return None + + nr_parts = len(line_parts) + if nr_parts < 7 : + logger.error("Error: invalid line format for Connecter." + line) + return None + + peer_ip = line_parts[nr_parts-1]; + direction = log_msg_dir["RECEIVE"]; + return (timestamp, direction, peer_ip, None, msg_db_code["BT_INTERESTED"], None, None, None, 0) + + +""" + sample line: 14-11-2009 23:11:13 connecter: Got HAVE( 14 ) from 141.85.37.41 + BitTorrent message format: have +""" +def tribler_parse_have_msg(line): + + if line.find(msg_types["BT_HAVE"]) == -1: + return None + + # the messages can also have the format: Downloader: got_have is invalid piece + #if line.find("invalid") != -1: + # return None # didn't decide yet what to do with it + + line_parts = re.split(" *", line) + timestamp = tribler_parse_timestamp(line_parts[0], line_parts[1]) + + if timestamp == None: + logger.error("Error: invalid date & time format for Connecter.") + return None + + nr_parts = len(line_parts) + if nr_parts < 9 : + logger.error("Error: invalid line format for Connecter." + line) + return None + + index = int(line_parts[5]) + peer_ip = line_parts[nr_parts-1]; + direction = log_msg_dir["RECEIVE"]; + return (timestamp, direction, peer_ip, None, msg_db_code["BT_HAVE"], index, None, None, 0) + +""" + sample line: 14-11-2009 23:11:25 connecter: Got BITFIELD from 141.85.37.41 +""" +def tribler_parse_bitfield_msg(line): + + if line.find(msg_types["BT_BITFIELD"]) == -1: + return None + + line_parts = re.split(" *", line) + timestamp = tribler_parse_timestamp(line_parts[0], line_parts[1]) + + if timestamp == None: + logger.error("Error: invalid date & time format for Connecter.") + return None + + nr_parts = len(line_parts) + if nr_parts < 7 : + logger.error("Error: invalid line format for Connecter." + line) + return None + + peer_ip = line_parts[nr_parts-1]; + direction = log_msg_dir["RECEIVE"]; + return (timestamp, direction, peer_ip, None, msg_db_code["BT_BITFIELD"], None, None, None, 0) + + +""" sample lines for sending and receiving requests: + 20-10-2009 12:56:39 Downloader: new_request 52 98304 16384 to 141.85.37.41 14398 + 27-11-2009 18:01:22 connecter: Got REQUEST( 1218 ) from 87.0.15.75 + BitTorrent protocol message: request: +""" +def tribler_parse_request_msg(line): + req_send = line.find(msg_types["BT_REQUEST_SEND"]) + req_recv = line.find(msg_types["BT_REQUEST_RECV"]) + if req_send == -1 and req_recv == -1: + return None + + msg_parts = 9 + file = "Connecter" + + if req_send != -1 : + msg_parts = 10 + file = "Downloader" + + timestamp = None + + line_parts = re.split(" *", line) + + if len(line_parts) < msg_parts : + logger.error("Error: invalid line format for " + file) + return None + + timestamp = tribler_parse_timestamp(line_parts[0], line_parts[1]) + if timestamp == None: + logger.error("Error: invalid line format for " + file) + return None + + # Send request message + if req_send != -1: + index = int(line_parts[4]) + begin = int(line_parts[5]) + length = int(line_parts[6]) + peer_ip = line_parts[8] + peer_port = int(line_parts[9]) + + direction = log_msg_dir["SEND"]; + return (timestamp, direction, peer_ip, peer_port, msg_db_code["BT_REQUEST"], index, begin, length, 0) + + # Receive request message + index = int(line_parts[5]) + peer_ip = line_parts[8] + direction = log_msg_dir["RECEIVE"]; + return (timestamp, direction, peer_ip, None, msg_db_code["BT_REQUEST"], index, None, None, 0) + + + +""" + sample line: 14-11-2009 23:11:13 connecter: Got PIECE( 141 ) from 141.85.37.41 + BitTorrent message format: piece +""" +def tribler_parse_piece_msg(line): + + if line.find(msg_types["BT_PIECE"]) == -1: + return None + + line_parts = re.split(" *", line) + timestamp = tribler_parse_timestamp(line_parts[0], line_parts[1]) + + if timestamp == None: + logger.error("Error: invalid date & time format for Connecter.") + return None + + nr_parts = len(line_parts) + if nr_parts < 9 : + logger.error("Error: invalid line format for Connecter."+ line) + return None + + index = int(line_parts[5]) + peer_ip = line_parts[nr_parts-1]; + direction = log_msg_dir["RECEIVE"]; + return (timestamp, direction, peer_ip, None, msg_db_code["BT_PIECE"], index, None, None, 0) + + +""" + sample line: 14-11-2009 23:11:24 sent cancel: 130: 114688-131072 + BitTorrent massage format: +""" +def tribler_parse_cancel_msg(line): + + if line.find(msg_types["BT_CANCEL"]) == -1: + return None + + line_parts = re.split(" *", line) + timestamp = tribler_parse_timestamp(line_parts[0], line_parts[1]) + + if timestamp == None: + logger.error("Error: invalid date & time format for Connecter.") + return None + + nr_parts = len(line_parts) + if nr_parts < 6 : + logger.error("Error: invalid line format for Connecter."+ line) + return None + + line_parts = re.split("[ :-]*", line) + index = int(line_parts[8]) + begin = int(line_parts[9]) + length = int(line_parts[10]) - begin + direction = log_msg_dir["SEND"]; + return (timestamp, direction, None, None, msg_db_code["BT_CANCEL"], index, begin, length, 0) + + + +def tribler_parse_line(line): + + result = tribler_parse_choke_msg(line) + if result != None: + return result + result = tribler_parse_have_msg(line) + if result != None: + return result + result = tribler_parse_bitfield_msg(line) + if result != None: + return result + result = tribler_parse_request_msg(line) + if result != None: + return result + result = tribler_parse_piece_msg(line) + if result != None: + return result + result = tribler_parse_cancel_msg(line) + if result != None: + return result + result = tribler_parse_interested_msg(line) + return result; + +def tribler_parse_status_file(dbw, client_session_id, filename): + + try: + fin = open(filename, "r") + while 1: + line = fin.readline() + if not line: + break + + line = line.strip() + + result = tribler_parse_line(line) + if result == None: + continue + + (timestamp, direction, peer_ip, peer_port, msg_type, index, begin, length, listen_port) = result + if DEBUG == True: + print result + dbw.add_verbose_message_datetime(client_session_id, timestamp, direction, peer_ip, peer_port, + msg_type, index, begin, length, listen_port) + + except IOError: + logger.error("Error processing file " + filename) + +def main_just_parse(): + filename = sys.argv[1] + client_session_id = 1 + tribler_parse_status_file(None, 1, filename) + +def main_with_DB(): + + try: + opts, args = getopt.getopt(sys.argv[1:], "hi:f:", ["help", + "id=", "file="]) + except getopt.GetoptError, err: + print str(err) + usage() + sys.exit(2) + + client_session_id = None + filename = None + database = None + + for o, a in opts: + if o in ("-h", "--help"): + usage() + sys.exit(0) + elif o in ("-i", "--id"): + client_session_id = int(a) + elif o in ("-f", "--file"): + filename = a + else: + assert False, "unhandled option" + + if client_session_id == None: + logger.error("Error: no client session id.") + sys.exit(2) + + if filename == None: + logger.error("Error: no status file.") + sys.exit(2) + + # no database passed as argument + if len(args) != 1: + logger.error("Error: no database file passed as argument.") + sys.exit(2) + database = args[0] + + dbc = DatabaseCommander(database) + + # check for client_session_id, swarm_id, btclient_id + cursor = dbc.select_client_sessions_by_id(client_session_id) + if cursor == None: + logger.error("Error: no client session id ("+ str(client_session_id) + ") in database." ) + sys.exit(2) + for session_row in cursor: + pass + + swarm_id = session_row[1] + btclient_id = session_row[2] + + cursor = dbc.select_swarms(swarm_id) + if cursor == None: + logger.error("Error: no swarm id ("+ str(swarm_id) +") in database." ) + + sys.exit(2) + for swarm_row in cursor: + pass + + cursor = dbc.select_btclients(btclient_id) + if cursor == None: + print "Error: no client id (%d) in database." % btclient_id + sys.exit(2) + for btclient_row in cursor: + pass + + print "Client session row is: " + print " ", session_row + print "Swarm row is: " + print " ", swarm_row + print "Client row is: " + print " ", btclient_row + print "\nContinue parsing on file %s? (y/n) " % filename, + try: + ans = sys.stdin.readline().strip() + if ans != "y": + sys.exit(0) + except IOError: + print "Error reading standard input." + sys.exit(2) + print "" + + # parse status file + dbw = DatabaseWriter(database) + tribler_parse_status_file(dbw, client_session_id, filename) + +if __name__ == "__main__": + sys.exit(main_with_DB()) + #sys.exit(main_just_parse()) + + + + + + diff --git a/ppf/log-parser-mysql/tribler/StatusParser.py b/ppf/log-parser-mysql/tribler/StatusParser.py new file mode 100644 index 0000000..f0b185a --- /dev/null +++ b/ppf/log-parser-mysql/tribler/StatusParser.py @@ -0,0 +1,269 @@ +#!/usr/bin/env python + +import sys +import getopt +import re +from DatabaseWriter import DatabaseWriter +from DatabaseCommander import DatabaseCommander +import julian +import datetime + +DEBUG = True + +files_sizes = {} # dictionary: key - filename, value - filesize + +def usage(): + print "Usage: python StatusParser.py -i|--id id status_file " + print "id:" + print "\t--id" + print "\t-i\t\tclient_session_id" + print "\tstatus_file:" + print "\t--file" + print "\t-f\t\tstatus_file for tribler" + print "\tdatabase\t\tSQLite database file" + print "\t--help" + print "\t-h\t\t\tprint this help screen" + + +""" All status messages contain a status string (e.g. DLSTATUS_DOWNLOADING). """ +def tribler_is_status_line(line): + if line.find("DLSTATUS_DOWNLOADING") > -1 or line.find("DLSTATUS_SEEDING") > -1: + return True + return False + +def tribler_is_single_download_line(line): + if line.find("SingleDownload") == -1: + return False + return True + +""" Parse a line with this format: SingleDownload: save_as( u'' '' ) + Saves the file name and size in the dictionary file_sizes. + If the line does not correspond to this format, it does nothing. +""" +def tribler_get_file_size(line): + index = -1 + parts = [] + if line.find("save_as") != -1: + parts = line.split("'") + files_sizes[parts[1]] = int(parts[2]) # saves the filename and its size in bytes + +def tribler_canon_num_peers(non_canon_value): + return int(non_canon_value) + +def tribler_canon_dht(non_canon_value): + return int(non_canon_value) + +# 119.51kb/s -> 119 +def tribler_canon_download_speed(non_canon_value): + return int(float(non_canon_value.strip("KB/s"))) + +# 12119.51kb/s -> 12119 +def tribler_canon_upload_speed(non_canon_value): + return int(float(non_canon_value.strip("KB/s"))) + +# 25% -> 25*file_size/100 +def tribler_canon_download_size(non_canon_value, filename): + return float(non_canon_value.strip("%")) * files_sizes[filename] / 100 + +# 492mb -> 492*1024*1024 +def tribler_canon_upload_size(non_canon_value): + pass +# 26.456787 -> 26 (seconds) +def tribler_canon_eta(non_canon_value): + if non_canon_value != 'None': + return int(float(non_canon_value)) + return None + +""" Get date and timestamp and transform it into datetime format. + Format: dd-mm-yyyy hh:mm:ss +""" +def tribler_parse_timestamp(date, time): + + date_array = date.split("-"); + time_array = time.split(":"); + if len(date_array) != 3 or len(time_array) != 3: + return None + + timestamp = datetime.datetime(int(date_array[2]), int(date_array[1]), int(date_array[0]), #year, month, day + int(time_array[0]), int(time_array[1]), int(time_array[2])) #hour, min, sec + return timestamp +# +# sample tribler status line +# 03-Nov-2009 12:18:55 aqua.mpeg DLSTATUS_DOWNLOADING 29.84% None up 0.00KB/s down 4414.39KB/s eta 12 peers 2 +# +def tribler_parse_status_line(line): + num_peers = 0 + dht = 0 + download_speed = 0 + upload_speed = 0 + download_size = 0 + upload_size = 0 + eta = 0 + filename = "" + timestamp = None + string_array = re.split("\ *", line) + + if DEBUG == True: + print "string_array is: ", string_array + if len(string_array) != 14: + print "Error: Invalid line format!" + return None + + # get timestamp and transform it in datetime format + timestamp= tribler_parse_timestamp(string_array[0], string_array[1]) + + filename = string_array[2] + + i = 3 + while i < len(string_array): #string_array: + if string_array[i] == "peers": + num_peers = tribler_canon_num_peers(string_array[i+1]) + if DEBUG == True: print "num_peers = %d" %(num_peers) + i = i + 2 + continue + if string_array[i] == "down": + download_speed = tribler_canon_download_speed(string_array[i+1]) + if DEBUG == True: print "download_speed = %d" %(download_speed) + i = i + 2 + continue + if string_array[i] == "up": + upload_speed = tribler_canon_upload_speed(string_array[i+1]) + if DEBUG == True: print "upload_speed= %d" %(upload_speed) + i = i + 2 + continue + if string_array[i] == "DLSTATUS_DOWNLOADING" or string_array[i] == "DLSTATUS_SEEDING": + download_size = tribler_canon_download_size(string_array[i+1], filename) + if DEBUG == True: print "download_size = %d" %(download_size) + i = i + 2 + continue + if string_array[i] == "eta": + eta = tribler_canon_eta(string_array[i+1]) + if DEBUG == True: print "eta = %s" %(eta) + i = i + 2 + continue + i = i + 1 + if DEBUG == True: + print "-----------------------------gata o linie----------------" + return (timestamp, num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta) + +def tribler_parse_status_file(dbw, client_session_id, filename): + + try: + fin = open(filename, "r") + while 1: + line = fin.readline() + if not line: + break + + line = line.strip() + if tribler_is_single_download_line(line) == True: + tribler_get_file_size(line) + + if tribler_is_status_line(line) == False: + continue + + (time, num_peers, dht, download_speed, upload_speed, download_size, upload_size, eta_time) = tribler_parse_status_line(line) + + if DEBUG == True: + print "(%d, %s, %s, %d kb/s, %d kb/s, %d bytes, %d bytes)" % (num_peers, time, eta_time, download_speed, upload_speed, + download_size, upload_size) + dbw.add_status_message_datetime(client_session_id, time, num_peers, dht, download_speed, upload_speed, + download_size, upload_size, eta_time) + + except IOError: + print "Error processing file %s." %filename + +def main_just_parse(): + filename = sys.argv[1] + client_session_id = 1 + tribler_parse_status_file(None, client_session_id, filename) + +def main_with_DB(): + + try: + opts, args = getopt.getopt(sys.argv[1:], "hi:f:", ["help", + "id=", "file="]) + except getopt.GetoptError, err: + print str(err) + usage() + sys.exit(2) + + client_session_id = None + filename = None + database = None + + for o, a in opts: + if o in ("-h", "--help"): + usage() + sys.exit(0) + elif o in ("-i", "--id"): + client_session_id = int(a) + elif o in ("-f", "--file"): + filename = a + else: + assert False, "unhandled option" + + if client_session_id == None: + print "Error: no client session id." + sys.exit(2) + + if filename == None: + print "Error: no status file." + sys.exit(2) + + # no database passed as argument + if len(args) != 1: + print "Error: no database file passed as argument." + sys.exit(2) + database = args[0] + + dbc = DatabaseCommander(database) + + # check for client_session_id, swarm_id, btclient_id + cursor = dbc.select_client_sessions_by_id(client_session_id) + if cursor == None: + print "Error: no client session id (%d) in database." % client_session_id + sys.exit(2) + for session_row in cursor: + pass + + swarm_id = session_row[1] + btclient_id = session_row[2] + + cursor = dbc.select_swarms(swarm_id) + if cursor == None: + print "Error: no swarm id (%d) in database." % swarm_id + sys.exit(2) + for swarm_row in cursor: + pass + + cursor = dbc.select_btclients(btclient_id) + if cursor == None: + print "Error: no client id (%d) in database." % btclient_id + sys.exit(2) + for btclient_row in cursor: + pass + + print "Client session row is: " + print " ", session_row + print "Swarm row is: " + print " ", swarm_row + print "Client row is: " + print " ", btclient_row + print "\nContinue parsing on file %s? (y/n) " % filename, + try: + ans = sys.stdin.readline().strip() + if ans != "y": + sys.exit(0) + except IOError: + print "Error reading standard input." + sys.exit(2) + print "" + + # parse status file + dbw = DatabaseWriter(database) + tribler_parse_status_file(dbw, client_session_id, filename) + +if __name__ == "__main__": + sys.exit(main_with_DB()) + # sys.exit(main_just_parse()) diff --git a/ppf/log-parser-mysql/tribler/make_db b/ppf/log-parser-mysql/tribler/make_db new file mode 100755 index 0000000..2cabfd4 --- /dev/null +++ b/ppf/log-parser-mysql/tribler/make_db @@ -0,0 +1,15 @@ +#!/bin/bash + +DB_NAME=test.db + +# remove database in case it exists +rm -f $DB_NAME + +# create database +pushd . &> /dev/null +cd ../../db && ./db_init ../../log-parser/tribler/$DB_NAME +popd &> /dev/null + +# fill database with test data +PYTHONPATH=../../db/ python ../../db/DatabaseAccess.py $DB_NAME + diff --git a/ppf/log-parser-mysql/tribler/merge_status_msg.sh b/ppf/log-parser-mysql/tribler/merge_status_msg.sh new file mode 100755 index 0000000..c126f9b --- /dev/null +++ b/ppf/log-parser-mysql/tribler/merge_status_msg.sh @@ -0,0 +1,10 @@ +#!/bin/bash + + +if test $# -lt 2 + then + echo "Usage: $0 - Copies file1 into file2" +else + cat $1 >> $2 +fi + diff --git a/ppf/log-parser-mysql/tribler/run_sample b/ppf/log-parser-mysql/tribler/run_sample new file mode 100755 index 0000000..1c9bec9 --- /dev/null +++ b/ppf/log-parser-mysql/tribler/run_sample @@ -0,0 +1,8 @@ +#!/bin/bash + +if test $# -lt 2 + then + echo "Usage: $0 " +else + PYTHONPATH=PYTHONPATH:../../db/ python StatusParser.py -i 1 -f $1 $2; +fi diff --git a/ppf/log-parser-mysql/tribler/run_sample_verbose b/ppf/log-parser-mysql/tribler/run_sample_verbose new file mode 100755 index 0000000..e1174ab --- /dev/null +++ b/ppf/log-parser-mysql/tribler/run_sample_verbose @@ -0,0 +1,9 @@ +#!/bin/bash + +if test $# -lt 2 + then + echo "Usage: $0 " +else + PYTHONPATH=PYTHONPATH:../../db/ python LogParser.py -i 1 -f $1 $2; +fi + diff --git a/ppf/sql/p2p-log-mysql.sql b/ppf/sql/p2p-log-mysql.sql new file mode 100644 index 0000000..d5e7e21 --- /dev/null +++ b/ppf/sql/p2p-log-mysql.sql @@ -0,0 +1,78 @@ +drop table if exists status_messages; +drop table if exists verbose_messages; +drop table if exists client_sessions; +drop table if exists btclients; +drop table if exists swarms; + +create table swarms( + id integer primary key auto_increment, + torrent text, + filesize integer check(filesize between 0 and 1000), + purpose text, + source text); + +create table btclients( + id integer primary key auto_increment, + name text, + language text, + dht integer check(dht between 0 and 1), + streaming integer check(streaming between 0 and 1)); + +create table client_sessions( + id integer primary key auto_increment, + swarm_id integer references swarms(id) on delete cascade on update cascade, + client_id integer references btclients(id) on delete cascade on update cascade, + system_os text, + system_os_version text, + system_ram integer check (system_ram between 0 and 32768), + system_cpu integer check (system_cpu between 100 and 10000), + public_ip text, + public_port integer check (public_port between 1 and 65535), + ds_limit integer check (ds_limit between 0 and 1000000), + us_limit integer check (us_limit between 0 and 1000000), + start_time datetime); + +create table status_messages ( + cs_id integer references client_sessions(id) on delete cascade on update cascade, + timestamp datetime, + peer_num integer check (peer_num between 0 and 100000), + dht integer check (dht between 0 and 100000), + download_speed integer check (download_speed between 0 and 1000000), + upload_speed integer check (upload_speed between 0 and 1000000), + download_size integer check(download_size between 0 and 100000000000), + upload_size integer check(upload_size between 0 and 100000000000), + eta integer); + +-- +-- direction = 0 -> receive (from peer) +-- direction = 1 -> send (to peer) +-- + +create table verbose_messages ( + cs_id integer references client_sessions(id) on delete cascade on update cascade, + timestamp datetime, + direction integer check(direction between 0 and 1), + peer_ip text, + peer_port integer check(peer_port between 1 and 65535), + message_type integer check (message_type between 0 and 100), + _index integer check (_index between 0 and 100000), + begin integer check (begin between 0 and 10000000), + length integer check (length between 0 and 10000000), + listen_port integer check(listen_port between 0 and 65535)); + +-- insert BitTorrent clients in `btclients` table + +insert into btclients(name, language, dht, streaming) + values('Tribler', 'Python', 1, 1); +insert into btclients(name, language, dht, streaming) + values('libtorrent', 'C++', 1, 0); +insert into btclients(name, language, dht, streaming) + values('Vuze', 'Java', 1, 1); +insert into btclients(name, language, dht, streaming) + values('Transmission', 'C', 1, 0); +insert into btclients(name, language, dht, streaming) + values('Aria', 'C', 1, 0); +insert into btclients(name, language, dht, streaming) + values('Mainline', 'Python', 1, 0); + +-- .genfkey --exec -- 2.20.1