From ba66ea83e1001fd3669685cac8e5006e07d78e76 Mon Sep 17 00:00:00 2001 From: Razvan Deaconescu Date: Sun, 7 Aug 2011 20:20:36 +0300 Subject: [PATCH] ppf: Add NOT NULL and DEFAULT constraints to MySQL SQL file. --- ppf/sql/p2p-log-mysql.sql | 60 +++++++++++++++++++-------------------- 1 file changed, 30 insertions(+), 30 deletions(-) diff --git a/ppf/sql/p2p-log-mysql.sql b/ppf/sql/p2p-log-mysql.sql index 83c8721..9d0b0be 100644 --- a/ppf/sql/p2p-log-mysql.sql +++ b/ppf/sql/p2p-log-mysql.sql @@ -20,27 +20,27 @@ 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), + id UNSIGNED INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, + name VARCHAR(100) NOT NULL, + language VARCHAR(100) NOT NULL, url VARCHAR(255), - dht_support BOOLEAN, - streaming_support BOOLEAN, - pxe_support BOOLEAN, + dht_support BOOLEAN NOT NULL DEFAULT FALSE, + streaming_support BOOLEAN NOT NULL DEFAULT FALSE, + pxe_support BOOLEAN NOT NULL DEFAULT FALSE, features VARCHAR(1024) ) ENGINE=InnoDB; CREATE TABLE swarms ( - id UNSIGNED INTEGER PRIMARY KEY AUTO_INCREMENT, + id UNSIGNED INTEGER NOT NULL 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, + 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, system_os VARCHAR(100), system_os_version VARCHAR(100), -- system RAM in megabytes @@ -55,17 +55,17 @@ CREATE TABLE client_sessions ( -- KB/s us_limit UNSIGNED INTEGER, start_time DATETIME, - dht_enabled BOOLEAN, - pxe_enabled BOOLEAN, - streaming_enabled BOOLEAN, + dht_enabled BOOLEAN NOT NULL DEFAULT FALSE, + pxe_enabled BOOLEAN NOT NULL DEFAULT FALSE, + streaming_enabled BOOLEAN NOT NULL DEFAULT FALSE, 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, + id UNSIGNED INTEGER 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, -- KB/s @@ -81,12 +81,12 @@ CREATE TABLE status_messages ( ) 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, + 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, + timestamp DATETIME NOT NULL, -- beware of IPv6 - peer_ip VARCHAR(100), - peer_port UNSIGNED SMALLINT, + peer_ip VARCHAR(100) NOT NULL, + peer_port UNSIGNED SMALLINT NOT NULL, -- KB/s download_speed UNSIGNED INTEGER, -- KB/s @@ -94,26 +94,26 @@ CREATE TABLE peer_status_messages ( ) ENGINE=InnoDB; CREATE TABLE transfer_directions ( - id UNSIGNED TINYINT PRIMARY KEY AUTO_INCREMENT, + id UNSIGNED TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT, value VARCHAR(100) NOT NULL ) ENGINE=InnoDB; CREATE TABLE message_types ( - id UNSIGNED TINYINT PRIMARY KEY AUTO_INCREMENT, + id UNSIGNED TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT, value VARCHAR(100) NOT NULL, -- message parameters such as index, begin, length, listen port - parameters VARCHAR(256) NOT NULL + parameters VARCHAR(256) ) 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, + 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, timestamp DATETIME, - direction_id UNSIGNED TINYINT REFERENCES transfer_directions(id), + direction_id UNSIGNED TINYINT NOT NULL REFERENCES transfer_directions(id), -- beware of IPv6 - peer_ip VARCHAR(100), - peer_port UNSIGNED SMALLINT, - message_type_id UNSIGNED TINYINT REFERENCES message_types(id), + 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, -- 2.20.1