From 506761bbdaa6f1b99ff6a66cb5a4929e58dbf3a5 Mon Sep 17 00:00:00 2001 From: Razvan Deaconescu Date: Mon, 22 Aug 2011 10:13:43 +0300 Subject: [PATCH] ppf: Add test SQL file. --- ppf/new/tests/p2p-init-test.sql | 58 +++++++++++ ppf/new/tests/p2p-log-sqlite.sql | 172 +++++++++++++++++++++++++++++++ 2 files changed, 230 insertions(+) create mode 100644 ppf/new/tests/p2p-init-test.sql create mode 100644 ppf/new/tests/p2p-log-sqlite.sql diff --git a/ppf/new/tests/p2p-init-test.sql b/ppf/new/tests/p2p-init-test.sql new file mode 100644 index 0000000..ce48d02 --- /dev/null +++ b/ppf/new/tests/p2p-init-test.sql @@ -0,0 +1,58 @@ +-- +-- Populate P2P database with test data. +-- + +-- insert swarms in `swarms` table + +INSERT INTO swarms(torrent_filename, data_size) +VALUES('DarkKnight.torrent', '123000'); +INSERT INTO swarms(torrent_filename, data_size) +VALUES('Fedora.torrent', '1024'); + +-- insert sessions in `client_sessions` table + +INSERT INTO client_sessions(swarm_id, btclient_id, system_os, + system_os_version, system_ram, system_cpu, public_ip, public_port, + ds_limit, us_limit, start_time) +VALUES ('1', '2', 'Linux', '2.6.26', '512', '1500', '141.85.224.205', + '50500', '512', '64', julianday('2010-10-09 12:43:21')); +INSERT INTO client_sessions(swarm_id, btclient_id, system_os, + system_os_version, system_ram, system_cpu, public_ip, public_port, + ds_limit, us_limit, start_time) +VALUES('2', '4', 'Linux', '2.6.26', '512', '1500', '141.85.224.209', + '40400', '512', '64', julianday('2010-10-09 12:29:41')); + +-- insert messages in `status_messages` table + +INSERT INTO status_messages(client_session_id, timestamp, num_peers, + num_dht_peers, download_speed, upload_speed, + download_size, upload_size) +VALUES('1', julianday('2010-09-23 09:32:43'), '222', '0', '213', + '56', '200', '300'); +INSERT INTO status_messages(client_session_id, timestamp, num_peers, + num_dht_peers, download_speed, upload_speed, + download_size, upload_size) +VALUES('2', julianday('2010-09-23 09:32:44'), '222', '0', '213', + '56', '200', '300'); + +-- insert messages in `peer_status_messages` table + +INSERT INTO peer_status_messages(client_session_id, timestamp, peer_ip, + peer_port, download_speed, upload_speed) +VALUES('1', julianday('2010-09-23 09:30:00'), '141.85.37.24', + '12345', '23', '74'); +INSERT INTO peer_status_messages(client_session_id, timestamp, peer_ip, + peer_port, download_speed, upload_speed) +VALUES('2', julianday('2010-09-23 09:30:05'), '141.85.37.29', + '13254', '32', '47'); + +-- insert messages in `verbose_messages` table + +INSERT INTO verbose_messages(client_session_id, timestamp, + transfer_direction_id, peer_ip, peer_port, message_type_id) +VALUES('1', julianday('2010-09-23 09:32:45'), '1', '127.0.0.1', + '1345', '2'); +INSERT INTO verbose_messages(client_session_id, timestamp, + transfer_direction_id, peer_ip, peer_port, message_type_id) +VALUES('2', julianday('2010-09-23 09:32:46'), '2', '127.0.0.1', + '1345', '3'); diff --git a/ppf/new/tests/p2p-log-sqlite.sql b/ppf/new/tests/p2p-log-sqlite.sql new file mode 100644 index 0000000..8470ff6 --- /dev/null +++ b/ppf/new/tests/p2p-log-sqlite.sql @@ -0,0 +1,172 @@ +-- +-- Create tables in BitTorrent log analysis database in SQLite. +-- Use indexes for fast access. Ported from MySQL script. +-- +-- 2011, Razvan Deaconescu, razvan.deaconescu@cs.pub.ro +-- + +DROP TABLE IF EXISTS btclients; +DROP TABLE IF EXISTS swarms; +DROP TABLE IF EXISTS client_sessions; +DROP TABLE IF EXISTS status_messages; +DROP TABLE IF EXISTS peer_status_messages; +DROP TABLE IF EXISTS transfer_directions; +DROP TABLE IF EXISTS message_types; +DROP TABLE IF EXISTS verbose_messages; + +CREATE TABLE btclients ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + name VARCHAR(100) NOT NULL, + language VARCHAR(100) NOT NULL, + url VARCHAR(255), + dht_support BOOLEAN NOT NULL DEFAULT 0, + streaming_support BOOLEAN NOT NULL DEFAULT 0, + pxe_support BOOLEAN NOT NULL DEFAULT 0, + features VARCHAR(1024) +); + +CREATE TABLE swarms ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + torrent_filename VARCHAR(255), + data_size INTEGER, + description VARCHAR(4096) +); + +CREATE TABLE client_sessions ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + swarm_id INTEGER NOT NULL REFERENCES swarms(id) ON DELETE CASCADE ON UPDATE CASCADE, + btclient_id INTEGER NOT NULL REFERENCES btclients(id) ON DELETE CASCADE ON UPDATE CASCADE, + system_os VARCHAR(100), + system_os_version VARCHAR(100), + -- system RAM in megabytes + system_ram SMALLINT, + -- system CPU in MHz + system_cpu SMALLINT, + -- beware of IPv6 + public_ip VARCHAR(100), + public_port SMALLINT, + -- KB/s + ds_limit INTEGER, + -- KB/s + us_limit INTEGER, + start_time DATETIME, + dht_enabled BOOLEAN NOT NULL DEFAULT 0, + pxe_enabled BOOLEAN NOT NULL DEFAULT 0, + streaming_enabled BOOLEAN NOT NULL DEFAULT 0, + features VARCHAR(1024), + description VARCHAR(4096) +); + +CREATE TABLE status_messages ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + client_session_id INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE, + timestamp DATETIME NOT NULL, + num_peers SMALLINT NOT NULL DEFAULT 0, + num_dht_peers SMALLINT NOT NULL DEFAULT 0, + -- KB/s + download_speed INTEGER NOT NULL DEFAULT 0, + -- KB/s + upload_speed INTEGER NOT NULL DEFAULT 0, + -- KB + download_size INTEGER NOT NULL DEFAULT 0, + -- KB + upload_size INTEGER NOT NULL DEFAULT 0, + -- seconds + eta INTEGER +); + +CREATE TABLE peer_status_messages ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + client_session_id INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE, + timestamp DATETIME NOT NULL, + -- beware of IPv6 + peer_ip VARCHAR(100) NOT NULL, + peer_port SMALLINT NOT NULL, + -- KB/s + download_speed INTEGER NOT NULL DEFAULT 0, + -- KB/s + upload_speed INTEGER NOT NULL DEFAULT 0 +); + +CREATE TABLE transfer_directions ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + value VARCHAR(100) NOT NULL +); + +CREATE TABLE message_types ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + value VARCHAR(100) NOT NULL, + -- message parameters such as index, begin, length, listen port + parameters VARCHAR(256) +); + +CREATE TABLE verbose_messages ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + client_session_id INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE, + timestamp DATETIME, + transfer_direction_id TINYINT NOT NULL REFERENCES transfer_directions(id), + -- beware of IPv6 + peer_ip VARCHAR(100) NOT NULL, + peer_port SMALLINT NOT NULL, + message_type_id TINYINT NOT NULL REFERENCES message_types(id), + _index INTEGER, + begin INTEGER, + length INTEGER, + listen_port SMALLINT +); + +-- create indexes +CREATE INDEX swarm_id_index ON client_sessions(swarm_id); +CREATE INDEX btclient_id_index ON client_sessions(btclient_id); + +CREATE INDEX status_client_session_id_index ON status_messages(client_session_id); +CREATE INDEX status_timestamp_index ON status_messages(timestamp); +CREATE INDEX status_download_speed_index ON status_messages(download_speed); +CREATE INDEX status_upload_speed_index ON status_messages(upload_speed); + +CREATE INDEX peer_client_session_id_index ON peer_status_messages(client_session_id); +CREATE INDEX peer_timestamp_index ON peer_status_messages(timestamp); +CREATE INDEX peer_download_speed_index ON peer_status_messages(download_speed); +CREATE INDEX peer_upload_speed_index ON peer_status_messages(upload_speed); +CREATE INDEX peer_peer_ip_port_index ON peer_status_messages(peer_ip, peer_port); + +CREATE INDEX verbose_client_session_id_index ON verbose_messages(client_session_id); +CREATE INDEX verbose_timestamp_index ON verbose_messages(timestamp); +CREATE INDEX verbose_direction_id_index ON verbose_messages(transfer_direction_id); +CREATE INDEX verbose_peer_ip_port_index ON verbose_messages(peer_ip, peer_port); +CREATE INDEX verbose_message_type_id_index ON verbose_messages(message_type_id); + +-- insert BitTorrent clients in `btclients` table + +INSERT INTO btclients(name, language, url, dht_support, streaming_support) + VALUES('Tribler', 'Python', 'http://www.tribler.org/trac', 1, 1); +INSERT INTO btclients(name, language, url, dht_support, streaming_support) + VALUES('NextShare', 'Python', 'https://trac.p2p-next.org/', 1, 1); +INSERT INTO btclients(name, language, url, dht_support, streaming_support) + VALUES('libtorrent-rasterbar', 'C++', 'http://www.rasterbar.com/products/libtorrent/', 1, 1); +INSERT INTO btclients(name, language, url, dht_support, streaming_support) + VALUES('Vuze', 'Java', 'http://www.vuze.com/', 1, 1); +INSERT INTO btclients(name, language, url, dht_support, streaming_support) + VALUES('Transmission', 'C', 'http://www.transmissionbt.com/', 1, 0); +INSERT INTO btclients(name, language, url, dht_support, streaming_support) + VALUES('Aria', 'C', 'http://aria2.sourceforge.net/', 1, 0); +INSERT INTO btclients(name, language, url, dht_support, streaming_support) + VALUES('Mainline', 'Python', 'http://www.bittorrent.com/', 1, 0); + +-- insert into `transfer_directions` table + +INSERT INTO transfer_directions(value) VALUES('receive'); +INSERT INTO transfer_directions(value) VALUES('send'); + +-- insert into `message_types` table + +INSERT INTO message_types(value) VALUES('CHOKE'); +INSERT INTO message_types(value) VALUES('UNCHOKE'); +INSERT INTO message_types(value) VALUES('INTERESTED'); +INSERT INTO message_types(value) VALUES('NOT_INTERESTED'); +INSERT INTO message_types(value) VALUES('HAVE'); +INSERT INTO message_types(value) VALUES('BITFIELD'); +INSERT INTO message_types(value) VALUES('REQUEST'); +INSERT INTO message_types(value) VALUES('PIECE'); +INSERT INTO message_types(value) VALUES('CANCEL'); +INSERT INTO message_types(value) VALUES('DHT_PORT'); -- 2.20.1