From f17d60a15104281531f91bea905f795f3eb104fc Mon Sep 17 00:00:00 2001
From: Razvan Deaconescu <razvan.deaconescu@cs.pub.ro>
Date: Fri, 23 Oct 2009 16:13:54 +0300
Subject: [PATCH] added sql

---
 auto/sql/README             |  4 +++
 auto/sql/p2p-log-sqlite.sql | 55 +++++++++++++++++++++++++++++++++++++
 2 files changed, 59 insertions(+)
 create mode 100644 auto/sql/README
 create mode 100644 auto/sql/p2p-log-sqlite.sql

diff --git a/auto/sql/README b/auto/sql/README
new file mode 100644
index 0000000..17ba1ec
--- /dev/null
+++ b/auto/sql/README
@@ -0,0 +1,4 @@
+Run
+   sqlite3 -init p2p-log-sqlite.sql p2p-next.db
+to interpret SQL file, create a database in p2p-next.db database file and
+enter sqlite3 command-line.
diff --git a/auto/sql/p2p-log-sqlite.sql b/auto/sql/p2p-log-sqlite.sql
new file mode 100644
index 0000000..29843b6
--- /dev/null
+++ b/auto/sql/p2p-log-sqlite.sql
@@ -0,0 +1,55 @@
+drop table if exists status_messages;
+drop table if exists verbose_messages;
+drop table if exists client_session;
+drop table if exists btclients;
+drop table if exists swarms;
+
+create table swarms(
+	id integer primary key autoincrement, 
+	torrent text, 
+	filesize integer, 
+	purpose text, 
+	source text);
+
+create table btclients(
+	id integer primary key autoincrement, 
+	name text, 
+	language text, 
+	dht integer check(dht between 0 and 1),
+	streaming integer check(streaming between 0 and 1));
+
+create table client_session(
+	id integer primary key autoincrement, 
+	swarm_id integer references swarms(id), 
+	client_id integer references btclients(id),
+	system_os text,
+	system_os_version text,
+	system_ram integer,
+	system_cpu integer,
+	public_ip text,
+	public_port integer,
+	ds_limit integer,
+	us_limit integer,
+	start_time date);
+
+create table status_messages (
+	cs_id integer references client_session(id),
+	timestamp date,
+	peer_num integer,
+	dht integer,
+	download_speed integer,
+	upload_speed integer,
+	download_size integer,
+	upload_size integer,
+	eta date);
+
+create table verbose_messages (
+	cs_id integer references client_session(id),
+	timestamp date,
+	peer_ip integer,
+	peer_port integer check(peer_port between 1 and 65535),
+	message_type integer,
+	_index integer,
+	begin integer,
+	length integer,
+	listen_port integer check(listen_port between 1 and 65535));
-- 
2.20.1