From 27e8bb28e95c642d3cac671bb0ed0b1e1e68c4d4 Mon Sep 17 00:00:00 2001 From: Razvan Deaconescu Date: Sun, 7 Aug 2011 12:43:44 +0300 Subject: [PATCH] ppf: Test sample queries against existing database. --- ppf/db-mysql/sample-queries.sql | 56 ++++++++++++++++++--------------- 1 file changed, 30 insertions(+), 26 deletions(-) diff --git a/ppf/db-mysql/sample-queries.sql b/ppf/db-mysql/sample-queries.sql index 8486aeb..5ba85a7 100644 --- a/ppf/db-mysql/sample-queries.sql +++ b/ppf/db-mysql/sample-queries.sql @@ -12,8 +12,7 @@ select count(*) from client_sessions where swarm_id=1; select count(*) from status_messages where cs_id=1; -- select status messages in a given time range -select count(*) from status_messages where cs_id=1; -timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; +select count(*) from status_messages where timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; -- select status messages for a given swarm select count(*) from status_messages S, client_sessions C where S.cs_id=C.id and C.swarm_id=1; @@ -26,23 +25,29 @@ select count(*) from status_messages where download_speed>100 and download_speed -- certain threshold select count(*) from status_messages where upload_speed>100 and upload_speed<1000; --- select peer status messages in a given client session -select count(*) from peer_status_messages where cs_id=1; - --- select peer status messages in a given time range -select count(*) from peer_status_messages where timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; - --- select peer status messages for a given peer -select count(*) from peer_status_messages where peer_ip='89.245.110.136' and peer_port=49887; - --- select peer status messages for a given swarm -select count(*) from peer_status_messages P, client_sessions C where P.cs_id=C.id and C.swarm_id=1; - --- select peer status messages whose download speed is above a certain threshold -select count(*) from peer_status_messages where download_speed>100 and download_speed<1000; - --- select peer status messages whose upload speed is above a certain threshold -select count(*) from peer_status_messages where upload_speed>100 and upload_speed<1000; +---- select all peers in peer status messages +-- select distinct peer_ip,peer_port from peer_status_messages; +-- +---- select all peers in peer status messages for a given client session +-- select distinct peer_ip,peer_port from peer_status_messages where cs_id=1; +-- +---- select peer status messages in a given client session +-- select count(*) from peer_status_messages where cs_id=1; +-- +---- select peer status messages in a given time range +-- select count(*) from peer_status_messages where timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; +-- +---- select peer status messages for a given peer +-- select count(*) from peer_status_messages where peer_ip='89.245.110.136' and peer_port=49887; +-- +---- select peer status messages for a given swarm +-- select count(*) from peer_status_messages P, client_sessions C where P.cs_id=C.id and C.swarm_id=1; +-- +---- select peer status messages whose download speed is above a certain threshold +-- select count(*) from peer_status_messages where download_speed>100 and download_speed<1000; +-- +---- select peer status messages whose upload speed is above a certain threshold +-- select count(*) from peer_status_messages where upload_speed>100 and upload_speed<1000; -- select verbose messages for a given client session select count(*) from verbose_messages where cs_id=1; @@ -63,10 +68,10 @@ select count(*) from verbose_messages where peer_ip='89.245.110.136' and peer_po -- without an index, query takes around 2.0 seconds (on p2p-next-10) select distinct peer_ip,peer_port from verbose_messages; --- select all peers for a given client session +-- select all peers in verbose messages for a given client session select distinct peer_ip,peer_port from verbose_messages where cs_id=1; --- select all peers for a given swarm +-- select all peers in verbose messages for a given swarm select distinct peer_ip,peer_port from verbose_messages V, client_sessions C where V.cs_id=C.id and swarm_id=1; -- select number of verbose messages exchanged with each peer @@ -83,8 +88,7 @@ select count(*) from verbose_messages where cs_id=1 and message_type=2; select count(*) from verbose_messages where cs_id=1 and direction=1; -- select verbose messages in a given time range for a given client session -select count(*) from verbose_messages where cs_id=1 and message_type=2; -timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; +select count(*) from verbose_messages where cs_id=1 and timestamp<'2009-07-01 00:00:00' and timestamp>'2009-06-10 00:00:00'; -- select verbose messages for a given peer in a client session select count(*) from verbose_messages where cs_id=1 and peer_ip='89.245.110.136' and peer_port=49887; @@ -105,10 +109,10 @@ select count(*) from verbose_messages where peer_ip='89.245.110.136' and peer_po select count(*) from verbose_messages where direction=1 and peer_ip='89.245.110.136' and peer_port=49887; -- select verbose messages of a given type in a swarm -select count(*) from verbose_messages V, client_session C where C.id=V.cs_id and message_type=2 and swarm_id=1; +select count(*) from verbose_messages V, client_sessions C where C.id=V.cs_id and message_type=2 and swarm_id=1; -- select verbose messages of a given swarm in a given direction -select count(*) from verbose_messages V, client_session C where C.id=V.cs_id and direction=1 and swarm_id=1; +select count(*) from verbose_messages V, client_sessions C where C.id=V.cs_id and direction=1 and swarm_id=1; -- select verbose messages of a given type in a given direction for a given -- client session @@ -116,7 +120,7 @@ select count(*) from verbose_messages where cs_id=1 and direction=1 and message_ -- select verbose messages of a given type in a given direction for a given -- swarm -select count(*) from verbose_messages V, client_session C where C.id=V.cs_id and direction=1 and message_type=2 and swarm_id=1; +select count(*) from verbose_messages V, client_sessions C where C.id=V.cs_id and direction=1 and message_type=2 and swarm_id=1; -- select verbose messages of a given type for a given peer in a client session select count(*) from verbose_messages where cs_id=1 and peer_ip='89.245.110.136' and peer_port=49887 and message_type=2; -- 2.20.1