From b184914512ab3a3988d932169e031e0212abd9c7 Mon Sep 17 00:00:00 2001
From: Razvan Deaconescu <razvan.deaconescu@cs.pub.ro>
Date: Sun, 7 Aug 2011 17:28:26 +0300
Subject: [PATCH] Add new SQL file for MySQL database.

Add new tables, update database columns, add foreign keys, use InnoDB.
---
 ppf/sql/p2p-log-mysql-new.sql | 147 ++++++++++++++++++++++++++++++++++
 1 file changed, 147 insertions(+)
 create mode 100644 ppf/sql/p2p-log-mysql-new.sql

diff --git a/ppf/sql/p2p-log-mysql-new.sql b/ppf/sql/p2p-log-mysql-new.sql
new file mode 100644
index 0000000..83c8721
--- /dev/null
+++ b/ppf/sql/p2p-log-mysql-new.sql
@@ -0,0 +1,147 @@
+--
+-- Create tables in BitTorrent log analysis database in MySQL.
+-- Use indexes for fast access.
+--
+-- 2011, Mariana Marasoiu, mariana.marasoiu@gmail.com
+-- 2011, Razvan Deaconescu, razvan.deaconescu@cs.pub.ro
+--    - add foreign key support
+--    - add indexes
+--    - add message_types and peer_status_messages tables
+--    - update column types
+--
+
+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 UNSIGNED INTEGER PRIMARY KEY AUTO_INCREMENT,
+	name VARCHAR(100),
+	language VARCHAR(100),
+	url VARCHAR(255),
+	dht_support BOOLEAN,
+	streaming_support BOOLEAN,
+	pxe_support BOOLEAN,
+	features VARCHAR(1024)
+) ENGINE=InnoDB;
+
+CREATE TABLE swarms (
+	id UNSIGNED INTEGER PRIMARY KEY AUTO_INCREMENT,
+	torrent_filename VARCHAR(255),
+	data_size UNSIGNED INTEGER,
+	description VARCHAR(4096)
+) ENGINE=InnoDB;
+
+CREATE TABLE client_sessions (
+	id UNSIGNED INTEGER PRIMARY KEY AUTO_INCREMENT,
+	swarm_id UNSIGNED INTEGER REFERENCES swarms(id) ON DELETE CASCADE ON UPDATE CASCADE,
+	btclient_id UNSIGNED INTEGER REFERENCES btclients(id) ON DELETE CASCADE ON UPDATE CASCADE,
+	system_os VARCHAR(100),
+	system_os_version VARCHAR(100),
+	-- system RAM in megabytes
+	system_ram UNSIGNED SMALLINT,
+	-- system CPU in MHz
+	system_cpu UNSIGNED SMALLINT,
+	-- beware of IPv6
+	public_ip VARCHAR(100),
+	public_port UNSIGNED SMALLINT,
+	-- KB/s
+	ds_limit UNSIGNED INTEGER,
+	-- KB/s
+	us_limit UNSIGNED INTEGER,
+	start_time DATETIME,
+	dht_enabled BOOLEAN,
+	pxe_enabled BOOLEAN,
+	streaming_enabled BOOLEAN,
+	features VARCHAR(1024),
+	description VARCHAR(4096)
+) ENGINE=InnoDB;
+
+CREATE TABLE status_messages (
+	id UNSIGNED INTEGER PRIMARY KEY AUTO_INCREMENT,
+	client_session_id INTEGER REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE,
+	timestamp DATETIME,
+	num_peers UNSIGNED SMALLINT,
+	num_dht_peers UNSIGNED SMALLINT,
+	-- KB/s
+	download_speed UNSIGNED INTEGER,
+	-- KB/s
+	upload_speed UNSIGNED INTEGER,
+	-- KB
+	download_size UNSIGNED INTEGER,
+	-- KB
+	upload_size UNSIGNED INTEGER,
+	-- seconds
+	eta UNSIGNED INTEGER
+) ENGINE=InnoDB;
+
+CREATE TABLE peer_status_messages (
+	id UNSIGNED INTEGER PRIMARY KEY AUTO_INCREMENT,
+	client_session_id UNSIGNED INTEGER REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE,
+	timestamp DATETIME,
+	-- beware of IPv6
+	peer_ip VARCHAR(100),
+	peer_port UNSIGNED SMALLINT,
+	-- KB/s
+	download_speed UNSIGNED INTEGER,
+	-- KB/s
+	upload_speed UNSIGNED INTEGER
+) ENGINE=InnoDB;
+
+CREATE TABLE transfer_directions (
+	id UNSIGNED TINYINT PRIMARY KEY AUTO_INCREMENT,
+	value VARCHAR(100) NOT NULL
+) ENGINE=InnoDB;
+
+CREATE TABLE message_types (
+	id UNSIGNED TINYINT PRIMARY KEY AUTO_INCREMENT,
+	value VARCHAR(100) NOT NULL,
+	-- message parameters such as index, begin, length, listen port
+	parameters VARCHAR(256) NOT NULL
+) ENGINE=InnoDB;
+
+CREATE TABLE verbose_messages (
+	id UNSIGNED INTEGER PRIMARY KEY AUTO_INCREMENT,
+	client_session_id UNSIGNED INTEGER REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE,
+	timestamp DATETIME,
+	direction_id UNSIGNED TINYINT REFERENCES transfer_directions(id),
+	-- beware of IPv6
+	peer_ip VARCHAR(100),
+	peer_port UNSIGNED SMALLINT,
+	message_type_id UNSIGNED TINYINT REFERENCES message_types(id),
+	index UNSIGEND INTEGER,
+	begin UNSIGNED INTEGER,
+	length UNSIGNED INTEGER,
+	listen_port UNSIGNED SMALLINT
+) ENGINE=InnoDB;
+
+-- insert BitTorrent clients in `btclients` table
+-- FIXME: update INSERT statemets to use new `btclients` schema
+
+INSERT INTO btclients(name, language, dht, streaming)
+		VALUES('Tribler', 'Python', 1, 1);
+INSERT INTO btclients(name, language, dht, streaming)
+		values('libtorrent-rasterbar', '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);
+
+--
+-- insert into `transfer_directions` table
+-- direction = 0 -> receive (from peer)
+-- direction = 1 -> send (to peer)
+--
+
+--
+-- insert into `message_types` table
+--
-- 
2.20.1