ppf/new/tests/p2p-log-sqlite.sql

   1 --
   2 -- Create tables in BitTorrent log analysis database in SQLite.
   3 -- Use indexes for fast access. Ported from MySQL script.
   4 --
   5 -- 2011, Razvan Deaconescu, razvan.deaconescu@cs.pub.ro
   6 --
   7
   8 DROP TABLE IF EXISTS btclients;
   9 DROP TABLE IF EXISTS swarms;
  10 DROP TABLE IF EXISTS client_sessions;
  11 DROP TABLE IF EXISTS status_messages;
  12 DROP TABLE IF EXISTS peer_status_messages;
  13 DROP TABLE IF EXISTS transfer_directions;
  14 DROP TABLE IF EXISTS message_types;
  15 DROP TABLE IF EXISTS verbose_messages;
  16
  17 CREATE TABLE btclients (
  18         id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  19         name VARCHAR(100) NOT NULL,
  20         language VARCHAR(100) NOT NULL,
  21         url VARCHAR(255),
  22         dht_support BOOLEAN NOT NULL DEFAULT 0,
  23         streaming_support BOOLEAN NOT NULL DEFAULT 0,
  24         pxe_support BOOLEAN NOT NULL DEFAULT 0,
  25         features VARCHAR(1024)
  26 );
  27
  28 CREATE TABLE swarms (
  29         id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  30         torrent_filename VARCHAR(255),
  31         data_size INTEGER,
  32         description VARCHAR(4096)
  33 );
  34
  35 CREATE TABLE client_sessions (
  36         id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  37         swarm_id INTEGER NOT NULL REFERENCES swarms(id) ON DELETE CASCADE ON UPDATE CASCADE,
  38         btclient_id INTEGER NOT NULL REFERENCES btclients(id) ON DELETE CASCADE ON UPDATE CASCADE,
  39         system_os VARCHAR(100),
  40         system_os_version VARCHAR(100),
  41         -- system RAM in megabytes
  42         system_ram SMALLINT,
  43         -- system CPU in MHz
  44         system_cpu SMALLINT,
  45         -- beware of IPv6
  46         public_ip VARCHAR(100),
  47         public_port SMALLINT,
  48         -- KB/s
  49         ds_limit INTEGER,
  50         -- KB/s
  51         us_limit INTEGER,
  52         start_time DATETIME,
  53         dht_enabled BOOLEAN NOT NULL DEFAULT 0,
  54         pxe_enabled BOOLEAN NOT NULL DEFAULT 0,
  55         streaming_enabled BOOLEAN NOT NULL DEFAULT 0,
  56         features VARCHAR(1024),
  57         description VARCHAR(4096)
  58 );
  59
  60 CREATE TABLE status_messages (
  61         id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  62         client_session_id INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE,
  63         timestamp DATETIME NOT NULL,
  64         num_peers SMALLINT NOT NULL DEFAULT 0,
  65         num_dht_peers SMALLINT NOT NULL DEFAULT 0,
  66         -- KB/s
  67         download_speed INTEGER NOT NULL DEFAULT 0,
  68         -- KB/s
  69         upload_speed INTEGER NOT NULL DEFAULT 0,
  70         -- KB
  71         download_size INTEGER NOT NULL DEFAULT 0,
  72         -- KB
  73         upload_size INTEGER NOT NULL DEFAULT 0,
  74         -- seconds
  75         eta INTEGER
  76 );
  77
  78 CREATE TABLE peer_status_messages (
  79         id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  80         client_session_id INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE,
  81         timestamp DATETIME NOT NULL,
  82         -- beware of IPv6
  83         peer_ip VARCHAR(100) NOT NULL,
  84         peer_port SMALLINT NOT NULL,
  85         -- KB/s
  86         download_speed INTEGER NOT NULL DEFAULT 0,
  87         -- KB/s
  88         upload_speed INTEGER NOT NULL DEFAULT 0
  89 );
  90
  91 CREATE TABLE transfer_directions (
  92         id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  93         value VARCHAR(100) NOT NULL
  94 );
  95
  96 CREATE TABLE message_types (
  97         id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  98         value VARCHAR(100) NOT NULL,
  99         -- message parameters such as index, begin, length, listen port
 100         parameters VARCHAR(256)
 101 );
 102
 103 CREATE TABLE verbose_messages (
 104         id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 105         client_session_id INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE,
 106         timestamp DATETIME,
 107         transfer_direction_id TINYINT NOT NULL REFERENCES transfer_directions(id),
 108         -- beware of IPv6
 109         peer_ip VARCHAR(100) NOT NULL,
 110         peer_port SMALLINT NOT NULL,
 111         message_type_id TINYINT NOT NULL REFERENCES message_types(id),
 112         _index INTEGER,
 113         begin INTEGER,
 114         length INTEGER,
 115         listen_port SMALLINT
 116 );
 117
 118 -- create indexes
 119 CREATE INDEX swarm_id_index ON client_sessions(swarm_id);
 120 CREATE INDEX btclient_id_index ON client_sessions(btclient_id);
 121
 122 CREATE INDEX status_client_session_id_index ON status_messages(client_session_id);
 123 CREATE INDEX status_timestamp_index ON status_messages(timestamp);
 124 CREATE INDEX status_download_speed_index ON status_messages(download_speed);
 125 CREATE INDEX status_upload_speed_index ON status_messages(upload_speed);
 126
 127 CREATE INDEX peer_client_session_id_index ON peer_status_messages(client_session_id);
 128 CREATE INDEX peer_timestamp_index ON peer_status_messages(timestamp);
 129 CREATE INDEX peer_download_speed_index ON peer_status_messages(download_speed);
 130 CREATE INDEX peer_upload_speed_index ON peer_status_messages(upload_speed);
 131 CREATE INDEX peer_peer_ip_port_index ON peer_status_messages(peer_ip, peer_port);
 132
 133 CREATE INDEX verbose_client_session_id_index ON verbose_messages(client_session_id);
 134 CREATE INDEX verbose_timestamp_index ON verbose_messages(timestamp);
 135 CREATE INDEX verbose_direction_id_index ON verbose_messages(transfer_direction_id);
 136 CREATE INDEX verbose_peer_ip_port_index ON verbose_messages(peer_ip, peer_port);
 137 CREATE INDEX verbose_message_type_id_index ON verbose_messages(message_type_id);
 138
 139 -- insert BitTorrent clients in `btclients` table
 140
 141 INSERT INTO btclients(name, language, url, dht_support, streaming_support)
 142         VALUES('Tribler', 'Python', 'http://www.tribler.org/trac', 1, 1);
 143 INSERT INTO btclients(name, language, url, dht_support, streaming_support)
 144         VALUES('NextShare', 'Python', 'https://trac.p2p-next.org/', 1, 1);
 145 INSERT INTO btclients(name, language, url, dht_support, streaming_support)
 146         VALUES('libtorrent-rasterbar', 'C++', 'http://www.rasterbar.com/products/libtorrent/', 1, 1);
 147 INSERT INTO btclients(name, language, url, dht_support, streaming_support)
 148         VALUES('Vuze', 'Java', 'http://www.vuze.com/', 1, 1);
 149 INSERT INTO btclients(name, language, url, dht_support, streaming_support)
 150         VALUES('Transmission', 'C', 'http://www.transmissionbt.com/', 1, 0);
 151 INSERT INTO btclients(name, language, url, dht_support, streaming_support)
 152         VALUES('Aria', 'C', 'http://aria2.sourceforge.net/', 1, 0);
 153 INSERT INTO btclients(name, language, url, dht_support, streaming_support)
 154         VALUES('Mainline', 'Python', 'http://www.bittorrent.com/', 1, 0);
 155
 156 -- insert into `transfer_directions` table
 157
 158 INSERT INTO transfer_directions(value) VALUES('receive');
 159 INSERT INTO transfer_directions(value) VALUES('send');
 160
 161 -- insert into `message_types` table
 162
 163 INSERT INTO message_types(value) VALUES('CHOKE');
 164 INSERT INTO message_types(value) VALUES('UNCHOKE');
 165 INSERT INTO message_types(value) VALUES('INTERESTED');
 166 INSERT INTO message_types(value) VALUES('NOT_INTERESTED');
 167 INSERT INTO message_types(value) VALUES('HAVE');
 168 INSERT INTO message_types(value) VALUES('BITFIELD');
 169 INSERT INTO message_types(value) VALUES('REQUEST');
 170 INSERT INTO message_types(value) VALUES('PIECE');
 171 INSERT INTO message_types(value) VALUES('CANCEL');
 172 INSERT INTO message_types(value) VALUES('DHT_PORT');