From 0d4d4c2c5b4fa1cff8624e36e2abc9b0229455ac Mon Sep 17 00:00:00 2001 From: root p2p-next-02 Date: Sun, 7 Aug 2011 21:48:06 +0300 Subject: [PATCH] ppf: Fix SQL script. Works in real world. Correct MySQL syntax is INTEGER UNSIGNED instead of UNSIGNED INTEGER. index is a reserver keyword in MySQL also. Use _index for column name. Create p2p_logs_test database for testing. --- ppf/sql/p2p-log-mysql.sql | 70 +++++++++++++++++++-------------------- 1 file changed, 35 insertions(+), 35 deletions(-) diff --git a/ppf/sql/p2p-log-mysql.sql b/ppf/sql/p2p-log-mysql.sql index b3fdfe4..32fe4a8 100644 --- a/ppf/sql/p2p-log-mysql.sql +++ b/ppf/sql/p2p-log-mysql.sql @@ -20,7 +20,7 @@ DROP TABLE IF EXISTS message_types; DROP TABLE IF EXISTS verbose_messages; CREATE TABLE btclients ( - id UNSIGNED INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, + id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, language VARCHAR(100) NOT NULL, url VARCHAR(255), @@ -31,29 +31,29 @@ CREATE TABLE btclients ( ) ENGINE=InnoDB; CREATE TABLE swarms ( - id UNSIGNED INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, + id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, torrent_filename VARCHAR(255), - data_size UNSIGNED INTEGER, + data_size INTEGER UNSIGNED, description VARCHAR(4096) ) ENGINE=InnoDB; CREATE TABLE client_sessions ( - id UNSIGNED INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, - swarm_id UNSIGNED INTEGER NOT NULL REFERENCES swarms(id) ON DELETE CASCADE ON UPDATE CASCADE, - btclient_id UNSIGNED INTEGER NOT NULL REFERENCES btclients(id) ON DELETE CASCADE ON UPDATE CASCADE, + id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, + swarm_id INTEGER UNSIGNED NOT NULL REFERENCES swarms(id) ON DELETE CASCADE ON UPDATE CASCADE, + btclient_id INTEGER UNSIGNED 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 UNSIGNED SMALLINT, + system_ram SMALLINT UNSIGNED, -- system CPU in MHz - system_cpu UNSIGNED SMALLINT, + system_cpu SMALLINT UNSIGNED, -- beware of IPv6 public_ip VARCHAR(100), - public_port UNSIGNED SMALLINT, + public_port SMALLINT UNSIGNED, -- KB/s - ds_limit UNSIGNED INTEGER, + ds_limit INTEGER UNSIGNED, -- KB/s - us_limit UNSIGNED INTEGER, + us_limit INTEGER UNSIGNED, start_time DATETIME, dht_enabled BOOLEAN NOT NULL DEFAULT FALSE, pxe_enabled BOOLEAN NOT NULL DEFAULT FALSE, @@ -63,61 +63,61 @@ CREATE TABLE client_sessions ( ) ENGINE=InnoDB; CREATE TABLE status_messages ( - id UNSIGNED INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, + id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, client_session_id INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE, timestamp DATETIME NOT NULL, - num_peers UNSIGNED SMALLINT, - num_dht_peers UNSIGNED SMALLINT, + num_peers SMALLINT UNSIGNED, + num_dht_peers SMALLINT UNSIGNED, -- KB/s - download_speed UNSIGNED INTEGER, + download_speed INTEGER UNSIGNED, -- KB/s - upload_speed UNSIGNED INTEGER, + upload_speed INTEGER UNSIGNED, -- KB - download_size UNSIGNED INTEGER, + download_size INTEGER UNSIGNED, -- KB - upload_size UNSIGNED INTEGER, + upload_size INTEGER UNSIGNED, -- seconds - eta UNSIGNED INTEGER + eta INTEGER UNSIGNED ) ENGINE=InnoDB; CREATE TABLE peer_status_messages ( - id UNSIGNED INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, - client_session_id UNSIGNED INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE, + id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, + client_session_id INTEGER UNSIGNED 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 UNSIGNED SMALLINT NOT NULL, + peer_port SMALLINT UNSIGNED NOT NULL, -- KB/s - download_speed UNSIGNED INTEGER, + download_speed INTEGER UNSIGNED, -- KB/s - upload_speed UNSIGNED INTEGER + upload_speed INTEGER UNSIGNED ) ENGINE=InnoDB; CREATE TABLE transfer_directions ( - id UNSIGNED TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT, + id TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, value VARCHAR(100) NOT NULL ) ENGINE=InnoDB; CREATE TABLE message_types ( - id UNSIGNED TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT, + id TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, value VARCHAR(100) NOT NULL, -- message parameters such as index, begin, length, listen port parameters VARCHAR(256) ) ENGINE=InnoDB; CREATE TABLE verbose_messages ( - id UNSIGNED INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, - client_session_id UNSIGNED INTEGER NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE, + id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, + client_session_id INTEGER UNSIGNED NOT NULL REFERENCES client_sessions(id) ON DELETE CASCADE ON UPDATE CASCADE, timestamp DATETIME, - transfer_direction_id UNSIGNED TINYINT NOT NULL REFERENCES transfer_directions(id), + transfer_direction_id TINYINT UNSIGNED NOT NULL REFERENCES transfer_directions(id), -- beware of IPv6 peer_ip VARCHAR(100) NOT NULL, - peer_port UNSIGNED SMALLINT NOT NULL, - message_type_id UNSIGNED TINYINT NOT NULL REFERENCES message_types(id), - index UNSIGEND INTEGER, - begin UNSIGNED INTEGER, - length UNSIGNED INTEGER, - listen_port UNSIGNED SMALLINT + peer_port SMALLINT UNSIGNED NOT NULL, + message_type_id TINYINT UNSIGNED NOT NULL REFERENCES message_types(id), + _index INTEGER UNSIGNED, + begin INTEGER UNSIGNED, + length INTEGER UNSIGNED, + listen_port SMALLINT UNSIGNED ) ENGINE=InnoDB; -- insert BitTorrent clients in `btclients` table -- 2.20.1